SUM across multiple sheets

[JARichard74]

Workbook that has a Template sheet, Data sheet, Summary sheet and a sheet that gets added every day of the month. Each daily sheet is named e.g., 1-september, 2-september, etc. I want to add all J26 cells across all sheets where the formula adjusts to the last day i.e., on Sep 5 it would sum Sep 1 to 5, Sep 6 would sum Sep 1 to 6, etc. The total would be in the Summary sheet

Part 2 - In the same workbook, I would like to do a SUMIF. The items to sum are on each sheet in range N9:N18 and the sum range is on each sheet P9:P18 the criteria is that each item to sum in each sheet matches the list in the summary sheet (A11:A18). The total for each item would be in the Summary sheet (B11:B18)

 

[jasonb75]

These look like they should work but I have only done a very quick test. I've tried to keep the formulas dynamic without making them any more complex than necessary.

Note that due to the nature of the formulas, you will only see results in the correct month. e.g, the formulas for september will show errors from october 1st until august 31st. I could add a bit to the formula that will lock it to a specific month, but that would mean having a fixed date as a reference (ideally 1st day of the month that the whole workbook relates to), either in the formula, or in the summary sheet.

For the first one, try

=SUM(INDIRECT("'"&TEXT(SEQUENCE(DAY(TODAY()),,EOMONTH(TODAY(),-1)+1),"d-mmmm")&"'!J26"))

For the second one, try

=SUM(SUMIFS(INDIRECT("'"&TEXT(SEQUENCE(DAY(TODAY()),,EOMONTH(TODAY(),-1)+1),"d-mmmm")&"'!P9:P18"),INDIRECT("'"&TEXT(SEQUENCE(DAY(TODAY()),,EOMONTH(TODAY(),-1)+1),"d-mmmm")&"'!N9:N18"),A11))

 

[JARichard74]

Thanks. I will do some testing and get back to you. I should have mentioned that we create a new workbook every month so the potential error you mentioned for a different month should not be an issue. My template is in French so if I use "[$-C0C]d-mmmm" your formulas should work the same?

 

[JARichard74]

The formulas seem to work. At first, I had a #REF error. So, I created sheets to go to today, Sep 19. The formulas worked. Now I think I better understand your comment about the month. The formulas would only work in the current month. If I opened the book for Aug, I would likely get the #REF error? Ideally when I open the workbook for another month, I should see the totals as well. Thanks

 

[jasonb75]

Ideally when I open the workbook for another month, I should see the totals as well. Thanks

That is what I was referring to when I said this.

I could add a bit to the formula that will lock it to a specific month, but that would mean having a fixed date as a reference (ideally 1st day of the month that the whole workbook relates to), either in the formula, or in the summary sheet.

In order to do what you asked for originally it is necessary to use the TODAY() function for the current date. Naturally, that moves with month as well as day so to keep the month totals for September once we are actually in October you will need a fixed reference to a September date that the formula can use.

Assuming that you want a template summary sheet that you can use for any month, the best way would be to have that date in a cell somewhere in the summary sheet. If you enter the date directly into the formula then that would mean that you need to change all of the formulas each month, if you enter it into a cell, then you only need to change that one cell.

In both formulas below I've used $A$1 as the cell with the reference date, this can be changed as required. I haven't had chance to test them but they should be correct once you make any changes for language settings.

=SUM(INDIRECT("'"&TEXT(SEQUENCE(DAY(MAX(TODAY(),EOMONTH($A$1,0))),,$A$1),"d-mmmm")&"'!J26"))

=SUM(SUMIFS(INDIRECT("'"&TEXT(SEQUENCE(DAY(MAX(TODAY(),EOMONTH($A$1,0))),,$A$1),"d-mmmm")&"'!P9:P18"),INDIRECT("'"&TEXT(SEQUENCE(DAY(MAX(TODAY(),EOMONTH($A$1,0))),,$A$1),"d-mmmm")&"'!N9:N18"),A11))

 

[JARichard74]

Tested the formulas on the month of July and Aug which are complete to the 31st and it only adds the first 19 days.

 

[jasonb75]

Have you entered a reference date in A1?
You would need to enter 1st july 2022 and 1st august 2022 respectively (naturally these will need to be entered in the correct format for your French system settings).

 

[JARichard74]

Yes. When I evaluate the formula, you clearly see that it stops on 19 Jul and 19 Aug

 

[jasonb75]

Try changing MAX to MIN, (once in the first formula, twice in the second). I'm not able to test it at the moment but it looks like that's where it was going wrong.

 

[JARichard74]

Works for the current month and sheets from day 1 to today must be present. Works for other months however all sheets must be present. I will have to figure out a way for the month of May that will start on the 15th and September that will end on the 15th. The workbooks are used to track revenues at a campground that operates 15 May - 15 Sep