Counting Uniques - multiple criteria

Hi all. Got a long list of shipment records. Orders can have multiple line
items of varying quantities and can ship on multiple days.

I want to count how many unique orders were shipped on any given day for
each customer.

Order Line Customer Shipdate
A 1 Ted 1/12/05
B 1 Mike 1/12/05
A 2 Ted 1/12/05
C 1 Mike 1/12/05

Total unique orders shipped to Ted on 1/12/05 = 1
Total unique orders shipped to Mike on 1/12/05 = 2

Any help... much thanks!

Martin Just wrote...
>Hi all. Got a long list of shipment records. Orders can have multiple
line
>items of varying quantities and can ship on multiple days.
>
>I want to count how many unique orders were shipped on any given day
for
>each customer.
>
>Order Line Customer Shipdate
>A 1 Ted 1/12/05
>B 1 Mike 1/12/05
>A 2 Ted 1/12/05
>C 1 Mike 1/12/05
>
>Total unique orders shipped to Ted on 1/12/05 = 1
>Total unique orders shipped to Mike on 1/12/05 = 2

The general approach to counting uniques is

=SUMPRODUCT(1/COUNTIF(Range,Range))

With criteria, you need to use something like (assuming data in A2:D5)

=SUMPRODUCT((C2:C5="Ted")*(D2:D5=--"1/12/2005")
/(COUNTIF(A2:A5,IF((C2:C5="Ted")*(D2:D5=--"1/12/2005"),A2:A5))
+1-(C2:C5="Ted")*(D2:D5=--"1/12/2005")))

Hi

This, and more, is what a Pivot table does. Menu Data > Pivot table.

Adsmitted, you'll definitely hate it the first 25 minutes. After that you
can't believe that you managed without it for all those years.

HTH. Best wishes Harald

"Martin Just" <martinjust@yahoo.com> skrev i melding
news:%23wrWauO%23EHA.2196@TK2MSFTNGP14.phx.gbl...
> Hi all. Got a long list of shipment records. Orders can have multiple
line
> items of varying quantities and can ship on multiple days.
>
> I want to count how many unique orders were shipped on any given day for
> each customer.
>
> Order Line Customer Shipdate
> A 1 Ted 1/12/05
> B 1 Mike 1/12/05
> A 2 Ted 1/12/05
> C 1 Mike 1/12/05
>
> Total unique orders shipped to Ted on 1/12/05 = 1
> Total unique orders shipped to Mike on 1/12/05 = 2
>
> Any help... much thanks!
>
>
>
>