Counting Uniques - multiple criteria

  • Thread starter Thread starter Martin Just
  • Start date Start date
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!
 
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")))
 
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
 
Back
Top