Excel 2003 - Doing a Count based on criteria in multiple columns

[simonfisher]

My spreadsheet looks like this

A B C D D
ID # Sex Age Build Criteria
11111 F 17 Slim Brown Hair
Black Eyes
Wears Glasses
22222 M 23 Athletic Black Hair
Brown Hair
33333 M 40 Medium Brown hair
Brown Eyes
Speaks German

I am trying to obtain the number of Males and Females whose age is greater > 18, and the different criteria they have with a count of each of the different criteria.
I have tried a Pivot table but it doesn't take into consideration the blank rows. The number of criteria for each ID # can be 1 to many. I have tried a Pivot Table but it treats the blank rows as separate data items whereas in fact they belong to the ID #. There are several different criteria that one can choose from to use in the Criteria column

Any help would be greatly appreciated.

Thanx

 

[JVFriederick]

Please review faq68-660 titled "Summarize Data with a single equation".

Once you get the hang of setting up a 'matrix array formula' (my term for it anyway) you should be able to multiple criteria for almost any situation,

 

[xlbo]

You really need to fill in the blanks - then you can do this in about 30 seconds with a pivot table. See this FAQ for how: faq68-4741

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question