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



"ryguy7272" wrote:

Well, the results are just wrong. For one guy, named Ryan, not me though,
the result should be 32 unique numbers on Sheet2. The formula is giving me a
result of 30. I know this because I used the Advanced Filter to copy/paste
uniques to a new sheet. Also, some of the results are fractions, and I can
see this when I increase the decimalization. I should never have 12.33333
unique numbers; I should always have a whole number. I guess this is a
result of the division operator.

As I mentioned before, it is close, but not right.

Any other thoughts?

Thanks,
Ryan---


--
RyGuy


"Mike H" wrote:

Hi,

I can't see anything wrong with the formula. In what way are the numbers
'off a bit'?

Mike


"ryguy7272" wrote:

I have a summary sheet and a list of names, in ColumnC, on this sheet. Im
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---


--
RyGuy