# Counting a cell IF it contains one or more of a list of words

I have a spreadsheet which lists pupils who attend one of our homework centres. The spreadsheet lists the languages that the pupils speak, and I want to count the instances of pupils speaking specific languages, like the following example, where the pupil Language column has a named range set to registerListLanguage:

Pupil Name Pupil Language
Fake Name Albanian
A N Other Urdu
Pupil 1 Urdu, Punjabi, Italian
Pupil 2 Punjabi
Pupil 3 Urdu and Punjabi

This is easy where the pupils speak only one language (e.g. Albanian) with a simple =SUM(COUNTIFS(registerListLanguage, "Albanian")

However, in many instances, our pupils speak two or more languages, meaning that we need to list the languages they speak (like pupil 1 in the example above)

My overlords want me to be able to report on pupils who speak Urdu OR Punjabi, and I'm really struggling to get an accurate count... At the moment I am using the following formula:

``````=SUM(COUNTIFS(registerListLanguage, {"*Urdu*","*Punjabi*"}))
``````

But that is counting pupils who speak BOTH Punjabi AND Urdu twice, so Pupil 1 and Pupil 3 in the above example are counted twice...

I've been struggling all afternoon with how to get it to only count the cell ONCE if it contains EITHER Urdu OR Punjabi...

Can anybody point out where I've gone wrong?

Thanks!

You can subtract a COUNTIFS that count where they are both there:

``````=SUM(COUNTIFS(B2:B6, {"*Urdu*","*Punjabi*"}))-COUNTIFS(B2:B6,"*Urdu*",B2:B6,"*Punjabi*")
``````

Or use SUMPRODUCT that checks if either are in the cell and only counts it as 1 instead of 2:

``````=SUMPRODUCT(--((ISNUMBER(SEARCH("Urdu",B2:B6)))+(ISNUMBER(SEARCH("Punjabi",B2:B6)))>0))
``````

This creates two arrays of 1 and 0 then adds them together. If either line in the added array is greater than 0 it returns 1.

• PERFECT! I used SUMPRODUCT like you suggested and it all worked spot on! Thanks so much! :-) Dec 7, 2021 at 16:34