Sum of multiple project data starting on different dates

[neil2047]

This might be really simple but I cant seem to find the right formula
I have multiple projects in separate worksheets all have the same monthly format but start on different dates - Summary also in same format.
Therefore the 31/12/24 may be in column E for one project and column Z for another.
I need a formula that looks at a date criteria and sums all data in the projects on that date.
In simple terms if criteria on the column heading in the summary is 31/12/24 formula needs to return the sum of everything in all the projects under the 31/12/24 under that row.

 

[myall_blues]

You can use a formula of this form:

=SUMPRODUCT(SUMIF(INDIRECT("'" & A2:A3&"'!"&"B1:H1"),C2,INDIRECT("'" & A2:A3&"'!"&"B2:H3")))

Here are my mockups of your separate worksheets
Sheet1:

Book1
	A	B
1	Project 1	31/12/2024
2		4000
Sheet1

Sheet2:

Book1
	A	B	C	D	E	F
1	Project 2					31/12/2024
2						5000
Sheet2

Then on your summary sheet make a list of the sheet names somewhere and refer to that list using INDIRECT, followed by the range of the data area you wish to sum.

Book1
	A	B	C
1	Sheet list
2	Sheet1		31/12/2024
3	Sheet2		9000
Sheet3

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(SUMIF(INDIRECT("'" & A2:A3&"'!"&"B1:H1"),C2,INDIRECT("'" & A2:A3&"'!"&"B2:H3")))

Unfortunately SUMIFS and its mates don't work with 3D references.

 

[neil2047]

Thanks, Worked a treat

 

[myall_blues]

You’re welcome. Thanks for the feedback.