Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help - indirect & sumif & dates ..... | Excel Discussion (Misc queries) | |||
sumif/sumproduct formula help | Excel Worksheet Functions | |||
sumproduct or sumif formula help | Excel Discussion (Misc queries) | |||
Indirect Formula Problem | Excel Worksheet Functions | |||
include INDIRECT function into SUMPRODUCT formula | Excel Worksheet Functions |