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?

oh thanks it has been a while since i use excel, I'm so dumb — Minh Dương
– Minh Dương, Commented Apr 28, 2022 at 14:20

Shane S · Accepted Answer · 2022-05-12 07:21:41Z

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

answered May 12, 2022 at 7:21

Shane S

2,3633 gold badges25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel COUNTIF for comma separated values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related