|
COUNTIF formula in multiple columns
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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
You need to use the SUMPRODUCT function.
=SUMPRODUCT((A1:A10="girl"),(B1:B10=6)) =SUMPRODUCT((A1:A10="girl),(B1:B10=6),(C1:C10="Lon don")) Dave -- Brevity is the soul of wit. "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
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 "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
I knew I screwed up the formula I gave. Ron's is correct. You need the
asterisk (*) not the comma.... Dave -- Brevity is the soul of wit. "Ron Coderre" wrote: 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 "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
Replace the comma with an asterisk, *
Sorry... -- Brevity is the soul of wit. "Dave F" wrote: You need to use the SUMPRODUCT function. =SUMPRODUCT((A1:A10="girl"),(B1:B10=6)) =SUMPRODUCT((A1:A10="girl),(B1:B10=6),(C1:C10="Lon don")) Dave -- Brevity is the soul of wit. "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
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" wrote: I knew I screwed up the formula I gave. Ron's is correct. You need the asterisk (*) not the comma.... Dave -- Brevity is the soul of wit. "Ron Coderre" wrote: 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 "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
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 -- Brevity is the soul of wit. "Ron Coderre" wrote: 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" wrote: I knew I screwed up the formula I gave. Ron's is correct. You need the asterisk (*) not the comma.... Dave -- Brevity is the soul of wit. "Ron Coderre" wrote: 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 "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
Thank you very much to you both. It works marvellously!
-- NitaMax "Ron Coderre" wrote: 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" wrote: I knew I screwed up the formula I gave. Ron's is correct. You need the asterisk (*) not the comma.... Dave -- Brevity is the soul of wit. "Ron Coderre" wrote: 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 "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
COUNTIF formula in multiple columns
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 "Dave F" wrote: 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 -- Brevity is the soul of wit. "Ron Coderre" wrote: 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" wrote: I knew I screwed up the formula I gave. Ron's is correct. You need the asterisk (*) not the comma.... Dave -- Brevity is the soul of wit. "Ron Coderre" wrote: 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 "NitaMax" wrote: 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(D2:D482,"G"). So far, my efforts to adapt this have been fruitless. Any help would be grately appreciated. -- NitaMax |
| All times are GMT +1. The time now is 09:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com