Count Unique Numbers if Names Match

[ryguy7272]

I have a summary sheet and a list of names, in ColumnC, on this sheet. I’m
trying to figure out a way to find a match in names in ColumnC of Sheet2, and
then count unique numbers, in Column G, for these names. I was experimenting
with this:
=SUMPRODUCT((Sheet2!$C$2:$C$1000=C57)/(COUNTIF(Sheet2!$G$2:$G$1000,Sheet2!$G$2:$G$1000&"")))

This comes pretty close, but the numbers are off a bit.

Any idea how to do this?


Thanks,
Ryan---

 

[Mike H]

Hi,

This 'may' be a slight improvement to eliminate blanks being counted

=SUMPRODUCT((Products!C2:C1000=C57)*(Products!G2:G1000<>"")/COUNTIF(Products!G2:G1000,Products!G2:G1000&""))

Mike

 

[Mike H]

Hi,

I don't doubt for a moment you are getting the results you say but I'm
baffled. If we consider how the formula works with a slightly modified
version for clarity

=SUMPRODUCT(--(Products!$C$2:$C$10=C57)/(COUNTIF(Products!$G$2:$G$10,Products!$G$2:$G$10&"")))


if c2 - c10 are all the same as c57 then this bit returns an array of 1s

this bit

(--(Products!$C$2:$C$10=C57)

returns

SUMPRODUCT({1;1;1;1;1;1;1;1;1}

if in the next column we have 5 number 1 and 4 number 2, this bit
COUNTIF(Products!$G$2:$G$10,Products!$G$2:$G$10&"")

returns
{5;5;5;5;5;4;4;4;4})

if we then product those 2 arrays we get

..2;.2;.2;.2;.2;,25;.25;.25;.25

if we then sum this array we get 2 for the 2 unique values so again i'm
baffled how this returns a decimal and hope someone can rescue us (me)

Mike

 

[Mike H]

Tom...40

Tracy ...40

Thanks for pointing that out, it's about the only thing I never tried to
make the formula error out but as soon as you have the reason for the
decimals is obvious.

Mike

 

[Ashish Mathur]

Hi,

You can also try this. This is a non-array formula solution

1. Assume that in sheet 2, you have data in E5:F9 as follows:

Name Number
Ashish 12
Mahesh 23
Ashish 34
Mahesh 12
Rajesh 12

2. In B33, enter data as follows Name, Occurrence, Count unique
3. In B4:B6, enter Ashish, Mahesh, Rajesh
4. In C4, enter =COUNTIF(Sheet2!$G$6:$G$10,Sheet2!G$6) and copy down till C6
5. In cell D4, enter
=DCOUNT(Sheet2!$C$5:$G$10,Sheet2!G$5,Sheet1!$B$3:C4)-SUM($D$3:$D3) and copy
down till D6

Please note that this formula will work only when the col. G entries in
sheet 1 are all numbers.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com