Here is a screen cap of the month tab, Dec and Jan look the same just different data.

Here is a screen cap of the reference and summary tab. I'm trying to populate the grey fields by calculating the totals from Dec & January based on the date ranges.

I am trying to figure out a way to use a formula for this scenario:

In "December 2016" and "January 2017" tabs, there is a row in each tab (ie row 2) that I want to sum. In Row 1 of each of those tabs are dates. I need it summed based on a date range that is broken down on a separate tab. On this "reference" tab, column A would have the beginning date, column B end date and this would continue for multiple rows for the calendar year. I could use column C (and onwards) to show which tabs to look into as well if it helps.

I'm sorry if I didn't explain this very well...hopefully someone can help? Please and thanks!

  • Not very clear what you are asking, an example file would help. FYI, to reference a cell on another worksheet, for example if you want a cell to show contents of cell A1 on tab Sheet2: =Sheet2!A1 – wysiwyg Feb 1 '17 at 23:30
  • I've added some screen captures - hopefully it makes more sense! Thank you! – rcnewbie Feb 1 '17 at 23:46

So basically you want to sum the values in tab Dec-2016 and Jan-2017 if they are included in the date range specified in colume A and B in Reference tab (i.e if date range is 12/31/2016 to 1/1/2017, values under 31st in tab Dec-2016 and under 1st in tab Jan-1017 will be sum)? If this is the case then use SUMIFS, but this requires changing the first row of tab Jan-2017 and Dec-2016 to DATE format.

For e.g: Beginning date: 12/11/2016, End date: 1/10/2017. On tab Reference:

=SUMIFS('Dec-2016'!B2:AF2,'Dec-2016'!B1:AF1,">="&B3,'Dec-2016'!B1:AF1,"<="&C3)+SUMIFS('Jan-2016'!B2:AF2,'Jan-2016'!B1:AF1,">="&B3,'Jan-2016'!B1:AF1,"<="&C3). This would sum all the values with the corresponding date that is 'greater or equal' to the beginning date and 'smaller or equal' to the end date.

  • Thank you for your response! Would you have an alternative method if the first row of tab Jan-2017 and Dec-2016 cannot be modified...? Sorry to make things difficult.... – rcnewbie Feb 2 '17 at 20:57
  • Hmm, this will be pretty hard. It could be done I guess but the formula would be very long and tidious. If you don't want to change the first row, a better way is to create a hidden row somewhere, maybe row 20, 30 ... and enter the date as DATE format, then apply the above formula with the B1:AF1 changed to B20:AF20 or B30:AF30 ... (i.e. your hidden row). – Minh Tri Tran Feb 3 '17 at 1:26
  • Ok - I think I can get a row added at the top for the Dec and Jan tabs for the formatted date fields. Would it be possible to also have the due amounts populate based on the due date. The empty grey fields highlighted on the reference tab (Feb 6, 10 and 13) to show the amount that is due. Thanks! – rcnewbie Feb 3 '17 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.