Microsoft Excel SUM sheets depending on date

Question

I am having a bit of a struggle with excel. I have a workbook with 20 sheets, that all have the same format and identical structure. I have a cell with a date and a table with stocks and their total. I need to SUM all the totals if the date of the cell in the sheet is today(or whatever date i would like). I have already tried with =SUMIF(Sheet1:Sheet!C2, D1(the date i set), Sheet1:Sheet3!B5) and couple of variatons of SUMIF but it keeps returning me #VALUE.

Here is a data example:

Sheet1:

|--|-----------|-----------|-----------|
|--|     A     |    B      |    C      |
|1 | Product   | Stock     |           |
|--|-----------|-----------|-----------|
|2 |    Shoes  |    34     |31-01-2018 |
|--|-----------|-----------|-----------|
|3 |    Hats   |    23     |           |
|--|-----------|-----------|-----------|
|4 |  Jackets  |    12     |           |
|--|-----------|-----------|-----------|
|5 |   Total   |    69     |           |
|--|-----------|-----------|-----------|

Sheet2:

|--|-----------|-----------|-----------|
|--|     A     |    B      |    C      |
|1 | Product   | Stock     |           |
|--|-----------|-----------|-----------|
|2 |    Shoes  |    38     |30-01-2018 |
|--|-----------|-----------|-----------|
|3 |    Hats   |    25     |           |
|--|-----------|-----------|-----------|
|4 |  Jackets  |    16     |           |
|--|-----------|-----------|-----------|
|5 |   Total   |    79     |           |
|--|-----------|-----------|-----------|

Sheet3:

|--|-----------|-----------|-----------|
|--|     A     |    B      |    C      |
|1 | Product   | Stock     |           |
|--|-----------|-----------|-----------|
|2 |    Shoes  |    30     |31-01-2018 |
|--|-----------|-----------|-----------|
|3 |    Hats   |    20     |           |
|--|-----------|-----------|-----------|
|4 |  Jackets  |    10     |           |
|--|-----------|-----------|-----------|
|5 |   Total   |    60     |           |
|--|-----------|-----------|-----------|

May be I should mention that the C2s are date formatted. How can I SUM the totals of Sheet1 and Sheet3 depending on the date 31-02-2018? I already searched a lot but no success. I am not sure if I am not using SUMIF properly or it's just not the right choice of formula. I would be grateful for any help. Thank you in advance!

You may want to change the date from the 31st February. :) Other than that you want it to then return the sum of 34+30 for the 31st and just 38 for the 30th? — Darren Bartrup-Cook
Add an extra column on each sheet where you apply the IF condition, then sum that column across sheets. — Excel Developers
I need to get the SUM of 34+30(the totals) only if the date in their sheet is the same as the one I am providing. And thank you, I will change it to 31-Jan :D — Gabriel Dichev
Your going to have sum up 20 sumifs like =Sumif(Sheet1!C2:C100, D1, Sheet1!B2:B100) + Sumif(Sheet2!C2:C100, D1, Sheet2!B2:B100) + ... Or have a sumif on each page that uses the one date then sum those up in your final tab. That, or you will have to write sum VBA, but.. that would get uglier than a good set of formulas to do this. — JNevill
You could consolidate your sheets using a Pivot Table that will be able to filter/group easily — Patrick Honorez

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-01-31T16:52:13

As @ExcelDevelopers indicated you could have a calculation on each sheet and then used a 3D formula to sum them across the sheets.

Using your example sheets as a starting point I'd add a new sheet called something like Results. In B2 I type Shoes in C2 I type 31/01/2018.

Before your daily sheets I'd add a blank sheet called Start and after your last daily sheet I'd add a blank sheet called End.

In cell D2 of each of the daily sheets I enter the formula: =SUMIFS($B$2:$B$5,$A$2:$A$5,Results!$B$2,$C$2:$C$5,Results!$C$2)
This will calculate the total for each day based on your criteria.

In the Results sheet I'd use the 3D formula =SUM(Start:End!$D$2) to sum each of the results from the daily sheets giving the final figure.

Edit: Just realised you're after the Totals and not specific items.
Use this formula for totals in cell D2 of each daily sheet: =SUMIF($C$2,Results!$C$2,$B$5)

Microsoft Excel SUM sheets depending on date

1 Answers