SUMIF and SUMPRODUCT with INDIRECT formula problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Afternoon from a sunny RSA,

Four sheets, three are input sheets and the last one is a summary sheet.
Sheets 1 to 3 are vehicle specific. Truck 1, Truck 2 and Truck 3.
One each sheet is three columns, Date, Driver and Kilometers travelled, so
therefore each row would have for example in cell A2=2007/08/16 B2=David
C2=162
Now each vehicle sheet would have similar information. Just the dates,
driver and kms would obviously change.
Driver David does not necessary always drive the same vehicle. His
co-workers are Paul and Simon. And they also switch vehicles.
I want on the summary sheet to show on a particular date that David drove so
many kilometers and the same calculation for Paul and Simon.

Hoping in anticipation.
 
This formula will calculate the km for Paul on 2007/08/16:
=SUMPRODUCT(--(Sheet1!A2:A9999=DATE(2007,08,16)), --(Sheet1!B2:B9999="Paul"),
Sheet1!C2:C9999) + SUMPRODUCT(--(Sheet2!A2:A9999=DATE(2007,08,16)), --(Sheet2!
B2:B9999="Paul"), Sheet2!C2:C9999) + SUMPRODUCT(--(Sheet3!A2:A9999=DATE(2007,
08,16)), --(Sheet3!B2:B9999="Paul"), Sheet3!C2:C9999)

What I would recommend is that you use one sheet for data entry, with one
additional column for the Truck number, then use a pivot table or data filter
to view the information by day, truck or driver. This will make your
spreadsheet more flexible, and will allow you to buy more camions without
having to change anything!

Stephane Quenson.
 
Back
Top