I have a column that has Order/Invoice numbers with multiples of this
number per the number of line items on the order. For instance if ABC
Company had 1 order and 5 items on that order, it would have 5
identical Order #'s. I am needing a funtion to quickly count the
number of unique Order #'s per Sales Rep.

I tried doing this in a Pivot table but it is counting all the invoice
numbers and over reporting the number of "deals" per rep when they sold
more than one item on an order.

Thanks,

PZan


--
PokerZan
------------------------------------------------------------------------
PokerZan's Profile: http://www.excelforum.com/member.php...o&userid=23480
View this thread: http://www.excelforum.com/showthread...hreadid=473459

Assuming that Column A contains the sales representative, and Column B
contains the unique order/invoice number, try...

=SUMPRODUCT(($A$1:$A$10="A")/COUNTIF($B$1:$B$10,$B$1:$B$10&""))

If you list your sales representatives in a column, let's say Column D,
enter the following formula in E1 and copy down:

=SUMPRODUCT(($A$1:$A$10=D1)/COUNTIF($B$1:$B$10,$B$1:$B$10&""))

Hope this helps!

In article ,
PokerZan
wrote:

I have a column that has Order/Invoice numbers with multiples of this
number per the number of line items on the order. For instance if ABC
Company had 1 order and 5 items on that order, it would have 5
identical Order #'s. I am needing a funtion to quickly count the
number of unique Order #'s per Sales Rep.

I tried doing this in a Pivot table but it is counting all the invoice
numbers and over reporting the number of "deals" per rep when they sold
more than one item on an order.

Thanks,

PZan