Formula to count a number of different variables

[Niall291283]

Hi

I am looking to create a formula that will count a number of different variables.

I need to count

How many children live in a house under the age of 10.
How many children over the age of 10, by ***.

I was thinking a countif formula but not sure if this is right??

Any help would be greatly appreciated.

[NBVC]

If you are counting based on multiple criteria, like age and s.e.x., and you have Excel 2003, then you need SUMPRODUCT.

e.g.

=SUMPRODUCT((A2:A100>=10)*(B2:B100="M"))

where A2:B100 contains the data, and "M" means male... but you can replace with a cell reference containing the "M".

For single criteria, COUNTIF should work

e.g.

=COUNTIF(A2:A100,"<10")

[Niall291283]

Thanks for this.

Would this work with multiple people, i.e. child 1, child 2, etc?

[NBVC]

It counts all male children over the age of 10.... is that what you mean?

[Niall291283]

Basically what I have is raw data concerning people living within a house.

I have a set of rules that need to be followed in order to calculate how many bedrooms are required in the house, i.e. 1 for mom & dad, 2 children under 10 no matter what *** can share, 2 children of same *** can share up to they are 16, different *** children can not share a room from the age of 10.

[NBVC]

You can set up a table on the side, and do the counting, then sum up to get total rooms...etc...

Can you post a sample workbook emulating your current database (no real names)? Showing some sample expected results.