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 Is The Only Answer
Anything below grade. Foundation walls, crawl space walls, anywhere in contact with or near the ground.
Rim joists and headers, where the assembly is thin and fully exposed.
Under a roof deck, where the vapour control has to be at the cold side.
Anywhere the depth available is limited and the R-value has to come out of a shallow cavity.
Anywhere moisture is already a known problem. In a fog belt that is a longer list than it would be inland.
Where Open Cell Earns Its Place
Interior partition walls, where the job is sound rather than heat.
Between storeys, for the same reason.
Deep, irregular cavities well inside the heated envelope, where the extra volume is cheap to fill and there is no vapour control requirement.
Attic roof decks in some assemblies, though whether that is appropriate depends on the roof and on what the vapour control strategy is.
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.