View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_] Ashish Mathur[_2_] is offline
external usenet poster
 
Posts: 1,766
Default Count Unique Numbers if Names Match

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 B3:D3, 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:C 4)-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

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