Sumproduct Help?

[mralmackay]

Don't know if it is sumproduct that would do what I want, but from
looking into previous posts this appears like it would be possible?

I have a list of data in columns A:C. What I need to be able to do is
count the number of unique approvers (column C) by the Purchase Order
reference in Column B. I was hoping to do this by a pivot-table but
have struggled as this would only give a true count of occurences and
I've seen similar posts that back this up.

So for the following data-set, I need to show the data as:

Ref: PO Number Unique Approvers
IR2/2008-42059 1230002119 2
IR3565026714-45827 1230002120 1
IR08/29539-43169 1230002121 1

Could anyone possibly help with this should appear as a formula?
Assuming this is to be applied as a 'helper column' principle I could
then use this in a pivot-table.

Thanks in advance. Al.

DATA SET (Column A : C)
Ref: PO Number Name
IR2/2008-42059 1230002119 User 3
IR2/2008-42059 1230002119 User 1
IR3565026714-45827 1230002120 User 2
IR3565026714-45827 1230002120 User 2
IR08/29539-43169 1230002121 User 1

 

[T. Valko]

Assume your data is in the range A2:C6 with no empty cells within the range.

PO = B2:B6
Name = C2:C6

You have the unique PO numbers in the range B10:B12.

Try this array formula** entered in cell C10 and copy down to C12:

=COUNT(1/FREQUENCY(IF(PO=B10,MATCH(Name,Name,0)),ROW(Name)-MIN(ROW(Name))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

mark

--
Biff
Microsoft Excel MVP

Assume your data is in the range A2:C6 with no empty cells within the
range.

PO = B2:B6
Name = C2:C6

You have the unique PO numbers in the range B10:B12.

Try this array formula** entered in cell C10 and copy down to C12:

=COUNT(1/FREQUENCY(IF(PO=B10,MATCH(Name,Name,0)),ROW(Name)-MIN(ROW(Name))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Herbert Seidenberg]

Pivot Table and source helper column:
http://www.freefilehosting.net/download/3fdbh

 

[mralmackay]

Pivot Table and source helper column:http://www.freefilehosting.net/download/3fdbh

Thanks to you both on this. Really appreciate your help. Have just
downloaded the file as well so this will be really useful in pulling
together a pivot-table to show this.

Thanks,Al.