You are here:

Excel/List totals for a given date

Advertisement


Question
HI Tom,

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

Answer
Steve,

It sounds like you want to sum on 2 conditions
Date and  Line


say

A2:  9/16/2013
B2:  Maersk
C2:  =sumifs(Mechanic1!G:G,Mechanic1!D:D,$A2,Mechanic1!C:C,$B2)+sumifs(Mechanic2!G:G,Mechanic2!D:D,$A2,Mechanic2!C:C,$B2)+sumifs(Mechanic3!G:G,Mechanic3!D:D,$A2,Mechanic3!C:C,$B2)+...+sumifs(Mechanic7!G:G,Mechanic7!D:D,$A2,Mechanic7!C:C,$B2)


so you would need to a formula like
sumifs(Mechanic1!G:G,Mechanic1!D:D,$A2,Mechanic1!C:C,B2)
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.

--
Regards,
Tom Ogilvy
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.