Sum across multiple sheets based on date

[maddogg126]

Hello,

I am trying to sum amounts across multiple sheets based on a date (typically the end of the month). There are over 100 sheets so did not want a huge sumif formula. Also, not all of the sheets will have certain dates, for instance a project may have started in 2024 but I would be looking for monthly activity for 2023. Thanks.

 

[HighAndWilder]

Hello,

I am trying to sum amounts across multiple sheets based on a date (typically the end of the month). There are over 100 sheets so did not want a huge sumif formula. Also, not all of the sheets will have certain dates, for instance a project may have started in 2024 but I would be looking for monthly activity for 2023. Thanks.

I like to have the data all in one sheet for ease of producing reports. If the amalgamation of the sheets was automated it would be easy to produce the figures. Is this acceptable / feasible for you?

Examples of your data wold be handy.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB - Excel Range to BBCode

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.

 

[maddogg126]

Below is a screen shot. I am looking to have all monthly billings accumulated across all of the sheets based on month end date. The formula would be looking at column E and H. Let me know if this helps.

 

[HighAndWilder]

Below is a screen shot. I am looking to have all monthly billings accumulated across all of the sheets based on month end date. The formula would be looking at column E and H. Let me know if this helps.

View attachment 118145

View attachment 118146

Do you only ever have a maximum of 20 rows on each project?

 

[maddogg126]

Yes, that would be the max in most cases. The projects vary by length and contract amount, so it could be one row up to 20.

 

[HighAndWilder]

Yes, that would be the max in most cases. The projects vary by length and contract amount, so it could be one row up to 20.

And the date in column E can be anytime in the month and it is this date that will be needed for the summary?

 

[maddogg126]

Yes and the date will always be the last day of each respective month.

 

[HighAndWilder]

Yes and the date will always be the last day of each respective month.

I've had to put some data together to test it so I hope that I have it right. I assumed that you wanted to SUM the Amount column.

I have formulas for testing the data. Just press F9 to get a new set of data for test.

Put these three sheets into a new workbook for testing and familiarisation.

Sum across multiple sheets based on date.xlsx
	E	F							M	N	O
11	Date	Est.#							Amount	Outstanding	Notes
12	10/31/2024	1							550
13	07/31/2024	2							498
14	03/31/2024	3							698
15	12/31/2024	4							406
16	09/30/2024	5							519
17	10/31/2024	6							568
18	10/31/2024	7							419
19	08/31/2024	8							575
20	10/31/2024	9							485
21	04/30/2024	10							483
22	08/31/2024	11							151
23	07/31/2024	12							645
24	12/31/2024	13							685
25	07/31/2024	14							485
26	04/30/2024	15							563
27	11/30/2024	16							439
28	09/30/2024	17							573
29	12/31/2024	18							567
30	12/31/2024	19							609
31	04/30/2024	20							140
32	TOTAL
2415

Cell Formulas
Range	Formula
E12:E31	E12	=OFFSET(Report!$B$3,RANDBETWEEN(1,12),0)
F13:F31	F13	=F12+1
M12:M31	M12	=RANDBETWEEN(100,700)

and

Sum across multiple sheets based on date.xlsx
	E	F							M	N	O
11	Date	Est.#							Amount	Outstanding	Notes
12	04/24/2024	1							389
13	10/24/2024	2							681
14	03/24/2024	3							468
15	07/24/2024	4							409
16	08/24/2024	5							368
17	05/24/2024	6							313
18	02/24/2024	7							290
19	11/24/2024	8							639
20	03/24/2024	9							326
21	07/24/2024	10							302
22	06/24/2024	11							192
23	08/24/2024	12							306
24	09/24/2024	13							518
25	12/24/2024	14							468
26	10/24/2024	15							479
27	10/24/2024	16							183
28	03/24/2024	17							108
29	11/24/2024	18							369
30	12/24/2024	19							354
31	04/24/2024	20							619
32	TOTAL
2416

Cell Formulas
Range	Formula
E12:E31	E12	=OFFSET(Report!$B$3,RANDBETWEEN(1,12),0)
F13:F31	F13	=F12+1
M12:M31	M12	=RANDBETWEEN(100,700)

This is where the formulas are:

This formula does the work: =IFERROR(LET(f,VSTACK('2416:2415'!$E$12:$O$31),SUM(INDEX(FILTER(f,INDEX(f,,1)=Report!$B4),,9))),0)

If you want to sum sheets 2416 to 2411 you would change this to : =IFERROR(LET(f,VSTACK('2416:2411'!$E$12:$O$31),SUM(INDEX(FILTER(f,INDEX(f,,1)=Report!$B4),,9))),0)

It depends on the sheets from 2416 to 2411 being in an unbroken sequence of sheets.

You will need to test this on your data.

Sum across multiple sheets based on date.xlsx
	A	B	C	D
1
2		Billing Period	Total Amount
3
4		01/31/2024	0.00
5		02/29/2024	290.00
6		03/31/2024	1600.00
7		04/30/2024	2194.00
8		05/31/2024	313.00
9		06/30/2024	192.00
10		07/31/2024	2339.00
11		08/31/2024	1400.00
12		09/30/2024	1610.00
13		10/31/2024	3365.00
14		11/30/2024	1447.00
15		12/31/2024	3089.00
16
Report

Cell Formulas
Range	Formula
C4:C15	C4	=IFERROR(LET(f,VSTACK('2416:2415'!$E$12:$O$31),SUM(INDEX(FILTER(f,INDEX(f,,1)=Report!$B4),,9))),0)
B5:B15	B5	=EOMONTH(B4,1)

 

[maddogg126]

Thank you! I was able to get it to work. The only change I needed to make to =IFERROR(LET(f,VSTACK('2416:2415'!$E$12:$O$31),SUM(INDEX(FILTER(f,INDEX(f,,1)=Report!$B4),,9))),0) was changing the =Report!$B4 to =$B4

 

[HighAndWilder]

You will need to get the sheets references right too. f,VSTACK('2416:2415'!$E$1