ExcelBanter - COUNT IF in array

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - COUNT IF in array (https://www.excelbanter.com/excel-worksheet-functions/206563-count-if-array.html)

WildWill

COUNT IF in array

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

COUNT IF in array

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))

--
Biff
Microsoft Excel MVP

"WildWill" wrote in message
...
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.

Ashish Mathur[_2_]

COUNT IF in array

Hi,

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

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

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"WildWill" wrote in message
...
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.

All times are GMT +1. The time now is 06:10 AM.