Nested OR in countif function

Question

forum members

I am trying to use a nested OR in the countif function as follows:  countif(b8:b500, OR("NOB/PBL", "OOB/PBL", "OBL/PBL")).  It works ok with just one of the items e.g. countif(b8:b500, "NOB/PBL") but not with the nested OR.  I appreciate any help on this issue. 

Lemorse

Answer 1

COUNTIF() functions cannot use an OR() in its criteria but you could substitute with a simple SUMPRODUCT(). Try,

=SUMPRODUCT((B8:B500="NOB/PBL")+(B8:B500="OOB/PBL")+(B8:B500="OBL/PBL"))

... or,

=SUMPRODUCT(--(B8:B500={"NOB/PBL","OOB/PBL","OBL/PBL"}))

Answer 2

try this way:

=sumproduct(countif(b8:b500,{"nob/pbl";"oob/pbl";"obl/pbl"}))

or...

=sumproduct(countif(b8:b500,{"nob";"oob";"obl"}&"/pbl"))

hth,

hector.

Answer 3

To you who replied:

Thank you all for your suggestions.  I tried them and they all worked and I learned a new function.  This forum and you guys that contribute to it never fail to amaze me and I want you to know that here is one person who really appreciates the contribution you make.  Thank you.

Lemorse

Answer 4

Hi,

Try this

=SUMPRODUCT(1*(ISNUMBER(MATCH($B$8:$B$500,$A$1:$A$3,0))))

Hope this helps.