Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default Count Unique Numbers if Names Match

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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. 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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. 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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. 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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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. 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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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





  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default You're welcome!

Hi Biff, Mike,

I have same kind of problem. I need to get unique values in a column using sumproduct. I have lets say column named ranges as BU and Subscriber. Both are text.
I tried
=SUMPRODUCT(1/COUNTIF(Supervisor,Supervisor), -- (BU="NA"))
which gives me result in fractions
and
=COUNT(1/FREQUENCY(IF(BU="NA",Supervisor),Supervisor))
which gives result as 0. Bothe are incorrect

Pls help

Regds
Manish

On Friday, February 06, 2009 2:43 PM ryguy727 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



On Friday, February 06, 2009 3:07 PM Mike wrote:


Hi,

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

Mike


"ryguy7272" wrote:



On Friday, February 06, 2009 3:28 PM Mike wrote:


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:



On Friday, February 06, 2009 3:34 PM ryguy727 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:



On Friday, February 06, 2009 4:05 PM Mike wrote:


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:



On Friday, February 06, 2009 4:27 PM T. Valko wrote:


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:


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
...



On Friday, February 06, 2009 4:36 PM Mike wrote:


Thanks for pointing that out, it is 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:



On Friday, February 06, 2009 4:59 PM ryguy727 wrote:


That did it! Thanks so much Biff!!

Regards,
Ryan---

--
RyGuy


"Mike H" wrote:



On Friday, February 06, 2009 5:15 PM T. Valko wrote:


You're welcome!

--
Biff
Microsoft Excel MVP



On Saturday, February 07, 2009 12:00 AM Ashish Mathur wrote:


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
...




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can i count the number of unique names in a list Anvil22 Excel Discussion (Misc queries) 6 February 19th 08 02:36 AM
Count unique numbers Stilin Excel Worksheet Functions 2 September 19th 07 12:04 PM
Finding unique names--then converting those names to unique number Proton Excel Discussion (Misc queries) 7 June 13th 07 10:22 PM
Match and Count duplicate names Donald B Excel Worksheet Functions 4 June 9th 07 08:05 PM
Count Unique Names in list w/ Additional Criteria? Nodak Excel Worksheet Functions 1 January 26th 05 12:15 AM


All times are GMT +1. The time now is 12:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"