Open-cell foam absorbs airborne sound reasonably well and closed-cell does much less, because density and rigidity transmit vibration. Neither is a true soundproofing system. For serious sound control you need mass and decoupling, not insulation.
More detail
Insulation gets sold for sound control more often than it deserves, so it is worth separating what each material actually does. Open-cell foam is light and porous, which is what makes it a decent sound absorber within a cavity. It reduces airborne noise transmission through a wall or floor assembly to a modest degree, and it is the better of the two foams for this purpose. It works best on mid and high frequencies: voices, television, general household noise. Closed-cell foam is dense and rigid, and rigidity is the enemy of sound isolation because it couples the two sides of an assembly together and transmits vibration through it. It is chosen for moisture and R-value reasons, not acoustic ones. What neither does is stop low-frequency and impact noise: footsteps overhead, bass through a shared wall, road noise from heavy vehicles. That requires the two things insulation cannot supply. Mass, meaning added layers of dense material such as a second layer of drywall. And decoupling, meaning a structural break so vibration cannot travel directly across, achieved with resilient channel, sound clips, or staggered studs. A realistic specification for a room where sound genuinely matters: open-cell or mineral wool in the cavity for absorption, resilient channel or clips for decoupling, and a second layer of drywall with a damping compound between for mass. Insulation alone is one of three ingredients. Where foam does deliver an audible improvement without any of that: assemblies that were previously empty, and exterior noise entering through air leaks rather than through the wall itself. Sealing the leaks quiets a house noticeably, and that part is real. The most common acoustic complaint in Greater Columbus and Central Ohio is road noise in older stock near arterials. the mix of pre-war stock in the older Columbus neighborhoods, mid-century ranches in the inner suburbs, and large 1990s-onward subdivisions in the outer counties In those houses the audible win usually comes from sealing leaks around windows, sills, and the attic plane rather than from whatever fills the cavity.