How to obtain count when multiple values are met

**cynlpat** · 02-07-2011, 06:46 PM

Hi there,

I have tried the forums and haven't been able to make any of the suggestions work for me.

I would like to count values if multiple criteria are met in the following situation:

1. I would like to count the unique values in column A UNLESS the first 3 numbers are the same. If the first 3 numbers are the same, I only want to count it once. (I can break out the numbers and letters into separate columns if needed.)
2. -and- if column B has a name (non-blank value)
3. -and- reference column C, so that the appropriate count pulls over to the key.

(A key is to the right of the attached spreadsheet where I would like the counts to show. They are separated into "Plan" categories.)

Thank you!

C

**cynlpat** · 02-07-2011, 06:50 PM

Oh, and I am using Excel 2010; I just realized that my profile shows as Excel 2003.

**MarvinP** · 02-07-2011, 07:00 PM

Hi cynipat,
First try CountIfs where you can do multiple criteria or try a Pivot Table, which is very easy in 2010.

See the attached.
1. You can easily filter out Blank Names
2. You can Drop the row column selector and manually filter out 111 and 222 and AAA if needed
3. You can count either P or C and group all these by the Plan.

You can even use 2003 if you like.

**cynlpat** · 02-07-2011, 07:15 PM

Wow, thanks for replying so fast!

This spreadsheet needs to stay in the original format unfortunately, but thank you for forwarding the pivot table solution

The yellow highlighted boxes should show the number of occupants in a given plan, provided that there are not two people sharing 1 unit. If there are two people in 1 unit, I would still like the count to show as 1.

Any advice on how to pull this off is greaty appreciated!

**MarvinP** · 02-07-2011, 07:50 PM

Hi,

I'm confused on your "stay in the original format" above. You simply want the yellow cells to show the counts?

If that's true how about a little bigger sample of data. Perhaps one that has tripple numbers in the first 3 values? It was a little difficult knowing exactly what #3 meant from your first post.

The reason I gave a pivot table is I couldn't figure out what you really wanted counted.

**DonkeyOte** · 02-08-2011, 03:27 AM

Based on the example perhaps:

I4:
=SUMPRODUCT((($C$2:$C$17<>"")*($D$2:$D$17=TRIM($G3)))/COUNTIFS($A$2:$A$17,$A$2:$A$17&"",$D$2:$D$17,$D$2:$D$17&""))
copied to I6,I8,I10,I12,I14
would return 6,1,0,0,1,2 respectively