Counting ID appearances with parameters

**dems** · 03-16-2010, 02:35 PM

Hi guys,

Apologies for the poor title, wasnt really sure how to position this worksheet exercise I am totally stuck on.

I am trying to count the number of times a different ID appears in column 8 with respect to it also matching the same letter in column 1.

I have attached a worksheet with a couple of examples
1st. The only ID in column 8 is 990049032 for A in column 1
2nd. There are two IDs in col. 8 (990054301 and 990043126) for B in column 1

Truly appreciate any feedback!
D

**Leith Ross** · 03-16-2010, 05:50 PM

Hello dems,

It isn't clear to me from either your post or workbook what results you want . Can you post a sample table showing before and after results?

**dems** · 03-20-2010, 01:29 AM

Hi Leith Ross,

Attached an answers tab as requested in sheet 2.

As you can see, the corresponding column D/4 is simply counting the number of different ID numbers (column H/8) appear within all of the IDs which fall under column A/1.

Struggling to think of the logic to go through and doubting whether excel has a function to support this ultimate goal.

I really do appreciate help on this. Cheers,
D

**DonkeyOte** · 03-20-2010, 05:58 AM

I *think* you're saying you want a unique count of Column 8 values per Group in Column 1 ?

If as implied your data is sorted by Column 1 then perhaps you could get away with:

D2:
=IF($A2=$A1,$D1,SUMPRODUCT(($H2:INDEX($H:$H,MATCH($A2,$A:$A))<>"")/COUNTIF($H2:INDEX($H:$H,MATCH($A2,$A:$A)),$H2:INDEX($H:$H,MATCH($A2,$A:$A))&"")))
copied down

Note the above would return values where H is blank... this is to reduce calculation burden (ie unique calculation conducted only once per group).

The use of INDEX to reduce the range size being processed within the SUMPRODUCT itself ... if you really only have a hundred rows of data you could remove the INDEXes and apply the SUMPRODUCT to the entire range adding in a group test, ie:

D2:
=IF($A2=$A1,$D1,SUMPRODUCT(--($A2:$A$100=$A2),($H2:$H$100<>"")/COUNTIF($H2:$H$100,$H2:$H$100&"")))
copied down

**dems** · 03-21-2010, 02:07 PM

Wow awesome, works perfect. Just wish i totally understood how it worked

Originally Posted by DonkeyOte

I *think* you're saying you want a unique count of Column 8 values per Group in Column 1 ?

If as implied your data is sorted by Column 1 then perhaps you could get away with:

D2:
=IF($A2=$A1,$D1,SUMPRODUCT(($H2:INDEX($H:$H,MATCH($A2,$A:$A))<>"")/COUNTIF($H2:INDEX($H:$H,MATCH($A2,$A:$A)),$H2:INDEX($H:$H,MATCH($A2,$A:$A))&"")))
copied down

Note the above would return values where H is blank... this is to reduce calculation burden (ie unique calculation conducted only once per group).

The use of INDEX to reduce the range size being processed within the SUMPRODUCT itself ... if you really only have a hundred rows of data you could remove the INDEXes and apply the SUMPRODUCT to the entire range adding in a group test, ie:

D2:
=IF($A2=$A1,$D1,SUMPRODUCT(--($A2:$A$100=$A2),($H2:$H$100<>"")/COUNTIF($H2:$H$100,$H2:$H$100&"")))
copied down