The Comparison, Plainly
Closed cell runs roughly R-6 to R-7 an inch, easing toward R-6.5 as it ages. Open cell runs roughly R-3.5 to R-3.8.
Closed cell is its own vapour barrier at sufficient thickness. Open cell is not a vapour barrier at any thickness, and no amount of extra depth makes it one. That single line decides most specifications.
Both are air barriers.
Closed cell is rigid and adds structural stiffness. Open cell is soft and flexible.
Open cell absorbs sound well. Closed cell is dense and transmits it.
Open cell costs meaningfully less per unit of volume.
Where Closed Cell Spray Foam Insulation Is The Only Answer
Anything below grade needs closed cell, including foundation walls, crawl space walls, and anywhere in contact with or near the ground.
Rim joists and headers need it too, because the assembly there is thin and fully exposed.
Under a roof deck, the vapour control has to sit at the cold side, which calls for closed cell as well.
Anywhere the depth available is limited, the R-value has to come out of a shallow cavity, so closed cell is the only way to reach it.
Anywhere moisture is already a known problem also calls for closed cell. In a fog belt, that is a longer list than it would be inland.
Where Open Cell Spray Foam Insulation Earns Its Place
Interior partition walls are a good fit, because the job there is sound control rather than heat.
Between storeys, open cell works for the same reason.
Deep, irregular cavities well inside the heated envelope also suit open cell, because the extra volume is cheap to fill and there is no vapour control requirement.
Attic roof decks sometimes take open cell too, though whether that is appropriate depends on the roof and on the vapour control strategy chosen.
Used in the right place, it is the better value. Used against a foundation, it is a mistake that shows up as damp within a year.
Both, In One Assembly
A flash and batt or flash and fill assembly uses a shallow layer of closed cell against the sheathing to do the air and vapour control, then fills the rest of the cavity with something less expensive.
It is often the best value in a new wall, and it is worth asking about rather than accepting a full-depth quote by default.