COUNTIF formula in multiple columns

[NitaMax]

I have Gender, Age and City columns.
I need a COUNTIF formula that will enable me to count, for example:

- number of girls, aged 6
- number of girls, aged 6 and from London

The formula I used for the gender is =COUNTIF(D2482,"G").

So far, my efforts to adapt this have been fruitless.

Any help would be grately appreciated.

 

[Dave F]

You need to use the SUMPRODUCT function.

=SUMPRODUCT((A1:A10="girl"),(B1:B10=6))

=SUMPRODUCT((A1:A10="girl),(B1:B10=6),(C1:C10="London"))

Dave

 

[Ron Coderre]

Try something like this:

With
A1:A20 contains girl or boy values
B1:B20 contains age values
C1:C20 contains city names

AND
F1: girl
G1: 6
H1: London

This formula counts the records that match all 3 criteria:
I1: =SUMPRODUCT((A1:A20=F1)*(B1:B20=G1)*(C1:C20=H1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Dave F]

I knew I screwed up the formula I gave. Ron's is correct. You need the
asterisk (*) not the comma....

Dave

 

[Dave F]

Replace the comma with an asterisk, *

Sorry...

 

[Ron Coderre]

Actually, you can use the commas if you prefer, BUT you need to coeerce the
boolean (true/false) values to numeric, using the dbl-negative (--):

Your posted formula would need to be
=SUMPRODUCT(--(A1:A10="girl"),--(B1:B10=6),--(C1:C10="London"))

***********
Regards,
Ron

XL2002, WinXP

 

[Dave F]

Right, but it seems to me that using the asterisk makes for a more concise,
easy to understand formula. I know others are fans of the -- though.

Dave

 

[NitaMax]

Thank you very much to you both. It works marvellously!

 

[Ron Coderre]

Regarding:

Right, but it seems to me that using the asterisk makes for a more concise,
easy to understand formula. I know others are fans of the -- though.

I agree...most users understand the asterisk as a multiplication operator.
The purpose of the dbl-neg is not always so apparent. I only use it in
special circumstances when I need it.

***********
Regards,
Ron

XL2002, WinXP