View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default Count Unique Numbers if Names Match

You're welcome!

--
Biff
Microsoft Excel MVP


"ryguy7272" wrote in message
...
That did it! Thanks so much Biff!!

Regards,
Ryan---

--
RyGuy


"Mike H" wrote:

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

"T. Valko" wrote:

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