Countif with advanced criteria

[jeffreymb]

I want to count how many rows contain the name of a certain accountant in column A where column B contains Level 1 or Level 2 or Level 3 but not Level 6.

I could get it done with a long drawn out sumproduct but surely there is a better way.

 

[just_jon]

Put your accountant name in C1.

In D1:D3 enter the 3 Level names.

=SUMPRODUCT(--(A1:A100=C1),--(ISNUMBER(MATCH(B1:B100,C1:C3,0))))

 

[jeffreymb]

Thanks Jon, that did it for me!