Thread: Counting occurrences in a range with more than 1 criteria

Hi all,

I'm fairly new to Excel, and I need help trying to figure out a function that will count all occurrences of either "SME" or "TSC" or "ICU" in a range of cells.

The cells may have more than that text, but if one of them is in the cell within the range, I want it to count that cell.

Example Range:

A1 = SME / 2246
B1 = 2246 / TSC
C1 = 2246 / ICU
D1 = VAC
E1 = VAC

I want it to return the value 3 because the range contains 3 occurrences of either "SME", "TSC" or "ICU"

Can anyone help? I've looked on other forums all over the place and I couldn't find specifically what I was looking for.

Any help would be greatly appreciated!

Thanks in advance,

Anthony

Try:

=SUMPRODUCT(--(ISNUMBER(SEARCH({"SME","TSC","ICU"},A1:E1))))

COUNTIF will also work

=COUNTIF(B2:B451,"SME") will yield a numerical count of that criteria in a given range

(in this example, range is B2 to B451)



Ahh - re-read post - MORE than 1 criteria - sorry.

The OP wants to know if either of those 3 would appear in A1:E1 and count all... as I understood it... you could add 3 countifs like yours (but needs wildcard because there could be other text in the cells), or another alternative with COUNTIF

=SUM(COUNTIF(A1:E1,{"*SME*","*TSC*","*ICU*"}))

Originally Posted by NBVC

Try:

=SUMPRODUCT(--(ISNUMBER(SEARCH({"SME","TSC","ICU"},A1:E1))))

For this one to work you need the separators in the array to change for a horizontal range, i.e.

=SUMPRODUCT(--(ISNUMBER(SEARCH({"SME";"TSC";"ICU"},A1:E1))))

Note the semi-colons

because of the need to make those changes based on the orientation of the range, I'd say that the COUNTIF version is preferable assuming that's your only condition

Thanks daddylonglegs... should've thought of that.