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 of
state? i.e. Males/Females 20 -30, 30-40, 40-50 etc.... or are there
specific ages you are looking for? 20,21,22,and so on. Do you have a
table containing the criteria to reference in a formula? How is your
data set up and how would you like it to look when you are done? Some
examples would be helpful.



Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=527577

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.

"LLFigo" wrote:

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