Excel/List totals for a given date
I am using Excel 2010 & would like help with the following:
I have a workbook with multiple worksheets in it, each on is a mechanics name.
In column C of each sheet, a steamship line that the mechanic worked on is listed.
Examples are "Maersk", "Metro", "Railpool".
In column D, is the date the mechanic worked on it, and in column G is the total amount of the repair.
I have a sheet called "Index", which contains the names of each mechanic in cells A2:A8, & is named "SheetNames" via the name manager. This sheet also contains a named range called "SteamshipLine", and it refers to cells C2:C15, which lists all the Steamship lines that we repair.
I have a formula in a separate sheet which totals up all the repair totals by steamship line: =SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!C:C"),$A17,INDIRECT("'"&SheetNames&"'!G:G")))
I am trying to list the repairs instead by a given date. For example, I would like to list a date range starting in cell A40 of a separate worksheet called "Totals", and next to each date in the range, a formula would return the total repairs for each steamship line on that date:
9/16/13 Maersk $595.36
9/16/13 Metro $1200.
9/16/13 Railpool $1175.55
9/17/13 Maersk $625.16
9/17/13 Metro $1315.98
9/17/13 Railpool $1595.67
Would this be better solved via a macro, or would a function be able to handle it?
I apologize for the length of this, but would you be able to help me out?
Thanks in advance...
It sounds like you want to sum on 2 conditions
Date and Line
so you would need to a formula like
for each mechanic (looks like you have 7 (A2:A8) so seven sheets. the ... in the formula just indicates more of the same - replace those with formulas for the other sheets.
Note that I am not using SumIF. I am using SUMIFS with an S introduced in Excel 2007 and later and allows specifying multiple conditions.