| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF across Multiple Rows and Columns | Excel Worksheet Functions | |||
| Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
| Multiple COUNTIF formula | Excel Discussion (Misc queries) | |||
| adding row to forumla | Excel Discussion (Misc queries) | |||
| Adding columns (one with a formula) | New Users to Excel | |||
Linear Mode
