"Count If" 3 criterias are fulfilled

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a file with data (e.g. age, sex, town) and would like to use the
"count if" function to see the results for all possible combinations (e.g.
20,male, NY or 40,female, LA).

The list is dynamic and thus changes all the time.

Thank you for your help
 
How are you identifying the age criteria? Is it the same regardless o
state? i.e. Males/Females 20 -30, 30-40, 40-50 etc.... or are ther
specific ages you are looking for? 20,21,22,and so on. Do you have
table containing the criteria to reference in a formula? How is you
data set up and how would you like it to look when you are done? Som
examples would be helpful.



Stev
 
countif only accpets one criterion. You could either create a helper column
that combines the criteria you need or use the sumproduct function:
=sumproduct(--(a$1:a$1000=20),--(b$1:b$1000="Male"),--(c$1:c$1000="NY"))
If you're not absolutely stuck on using functions to do this, you could also
create a pivot table. Select all the relevant columns, then Data > Pivot
Table.... Drop age, sex and town into the row fields, then drop any column
in the data field (if necessary change the aggregate function to CountOf vs
SumOf)). Then Excel will create all the combinations for you. Only caveat
with Pivots is that they don't automatically update. When your data changes,
right-click in the Pivot table and select refresh.
 
Back
Top