COUNT IF in array

[WildWill]

Hi

I have the following data:

A B
22 Cat
29 Bat
22 Tax
24 Bat
22 Cat

I want to calculate the total number (COUNT) of the occurences of "Cat" in
Column B, where Column A's value is "22". I.e. the answer to the above
example will be 2.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A5=22),--(B1:B5="cat"))

Better to use cells to hold the criteria:

D1 = 22
E1 = cat

=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1))

 

[Ashish Mathur]

Hi,

You may also try the following array formula (Ctrl+Shift+Enter)

COUNT(IF((D37=22)*(E3:E7="Cat"),1))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com