Countnig Unique Invoice #'s in a Column

[PokerZan]

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

[Domenic]

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.1wfv6c_1128531907.0389@excelforum-nospam.com>,
PokerZan <PokerZan.1wfv6c_1128531907.0389@excelforum-nospam.com>
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