Excel COUNTIF for comma separated values

Question

I have the following range of values:

National museum of Natural History
Archaeological Museum, Art Museum, Agricultural Museum, Marine Museum
National museum of Natural History, Art Museum
National museum of Natural History, Archaeological Museum, Science Museum
Open-air Museum, Art Museum

This means there can be more than one value in one column, separated by commas.

I want to count each value, for example: all "Archaeological Museum" in this range. I have used the COUNTIF method but it seems not to be enough:

COUNTIF(A1:A6, "Archaeological Museum")

=> result: 0

=> expected result: 2

Is there an only way to spilt the column based on comma or is there any other ways without creating a new column?

Answer 1

This is not a dumb question. This one has been asked and answer many time prior.

I would do it like show on this answer