Counting Uniques - multiple criteria

M

Martin Just

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!
 
H

hrlngrv

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
Click to expand...

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")))
 
H

Harald Staff

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
 
Top