SUMIF and SUMPRODUCT with INDIRECT formula problem

[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.

 

[squenson via OfficeKB.com]

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.