SUMIF and SUMPRODUCT with INDIRECT formula problem
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. |
SUMIF and SUMPRODUCT with INDIRECT formula problem
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. Sunnyskies wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200708/1 |
All times are GMT +1. The time now is 01:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com