countif-multiple conditions

M

miteeka

Is there a way to count the number of something if the condition that you're
counting is more than one string of values?

ie. I want to count the number of times "Data" or "Function" occurs in a
given cell

I've tried =countif(B2:B200,"Data","Function") but it doesn't work. Any
suggestions? thanks
 
T

T. Valko

Try one of these:

=COUNTIF(B2:B200,"Data")+COUNTIF(B2:B200,"Function")

=SUM(COUNTIF(B2:B200,{"Data","Function"}))

Biff
 
V

Vergel Adriano

maybe someone will have a better or shorter answer, but I found this works

=SUMPRODUCT(ISNUMBER(FIND("Data", B2:B200))*ISNUMBER(FIND("Function",
B2:B200))*1)
 
V

Vergel Adriano

I think I might have misunderstood the question. The formula I gave will give
the count of cells that has both "Data" and "Function". This one will give
the count of cells that has "Data" or "Function". It's an array formula, so
enter by pressing CTRL+SHIFT+ENTER

=SUMPRODUCT(IF(ISNUMBER(FIND("Data", B2:B200)), TRUE,
IF(ISNUMBER(FIND("Function", B2:B200)), TRUE, FALSE))*1)
 
P

Pete_UK

How about:

=SUMPRODUCT((B2:B200="Data")+(B2:B200="Function"))

Hope this helps.

Pete
 
Top