Home 
Search 
Today's Posts 
#1




Count Unique Numbers if Names Match
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 
#2




Count Unique Numbers if Names Match
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. 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 
#3




Count Unique Numbers if Names Match
Hi,
This 'may' be a slight improvement to eliminate blanks being counted =SUMPRODUCT((Products!C2:C1000=C57)*(Products!G2:G 1000<"")/COUNTIF(Products!G2:G1000,Products!G2:G1000&"")) Mike "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. 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 
#4




Count Unique Numbers if Names Match
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. 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 
#5




Count Unique Numbers if Names Match
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. 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 
#6




Count Unique Numbers if Names Match
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 
#7




Count Unique Numbers if Names Match
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 
#8




Count Unique Numbers if Names Match
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 
#9




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 
#10




Count Unique Numbers if Names Match
Hi,
You can also try this. This is a nonarray 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: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. 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 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
how can i count the number of unique names in a list  Excel Discussion (Misc queries)  
Count unique numbers  Excel Worksheet Functions  
Finding unique namesthen converting those names to unique number  Excel Discussion (Misc queries)  
Match and Count duplicate names  Excel Worksheet Functions  
Count Unique Names in list w/ Additional Criteria?  Excel Worksheet Functions 