find a match in names in ColumnC of Sheet2,
and then count unique numbers

Try this array formula** :

=COUNT(1/FREQUENCY(IF(Sheet2!$C$2:$C$1000=C57,Sheet2!$G$2:$ G$1000),Sheet2!$G$2:$G$1000))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

The problem with this formula:

=SUMPRODUCT((Sheet2!$C$2:$C$1000=C57)/(COUNTIF(Sheet2!$G$2:$G$1000,Sheet2!$G$2:$G$1000&" ")))

If the same number is associated with more than 1 name then you get
incorrect results.

C57 = Tom

Tom...40
Tracy ...40

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
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---


--
RyGuy