Looking for an excel formula for my Sumifs of multiple sheets, multiple columns sum range, and multiple criteria.

[suri7891]

Hi There,

I am looking for an excel formula for my sumifs of Multiple sheets, multiple column sum range, and multiple criteria. I tried to use Indirect+sumifs+sumproduct. But I am unable to select the multiple column sum range in the excel.

I have uploaded 2 images to understand the requirement. I need to consolidate all the sheets sum into the consolidated sheet. Please help.

Regards,
Suresh

 

[etaf]

how you have setout a grid lookup would work
but that would just reproduce the table you have

is that what you are after ?

Two-Way Lookups in Excel

or maybe via a header and row
something like

Sumproduct grid based on Header.xlsx
	B	C	D	E	F	G	H	I	J
24								SUMPRODUCT(($D$17:$F$22)*($D$16:$F$16=$J18)*($B$17:$B$22=$I18)*($C$17:$C$22=L$16))
25			sale	return				sale	return
26		10/1/22	1	10
27		10/5/22	2	20			10/1/22	36	360
28		10/9/22	3	30
29		10/13/22	4	40
30		10/17/22	5	50
31		10/21/22	6	60
32		10/25/22	7	70
33		10/29/22	8	80
34		11/2/22	9	90
35		11/6/22	10	100
36		11/10/22	11	110
Sheet1

Cell Formulas
Range	Formula
I27:J27	I27	=SUMPRODUCT(($D$26:$F$36)*($D$25:$F$25=I25)*($C$26:$C$36>=$H27)*($C$26:$C$36<=EOMONTH($H27,0)))

 

[suri7891]

Hi there,

thanks, but above formula is not helping me for multiple sheets. I need to sum the values based on resource name and Project from multiple sheets.

Regards,
Suresh

 

[suri7891]

Hi There,

I might need the formula of combined of indirect+sumifs+sumproduct.

Regards,
Suresh

 

[etaf]

how are the multiple sheets laid out - in the same way - or mulitple different ways - a 3d sum may work if all the same format
=SUM(Sheet2:Sheet4!B2)

 

[suri7891]

Hi there,

I receive every sheet periodically. There, I need to consolidate these information into the Summary sheet or consolidate sheet. I have given you the example one, not the actual one.

Regards,
Suresh.K

 

[etaf]

I have given you the example one,

as they are the same layout then, all you need is to use the 3D SUM
as thats all you have provided
and so based on those images

i have made a sample based on your images - Jan/feb/mar sheets

so all you need is a 3d sum
=sum(Jan:Mar!b2)
copied across and down

i have put a copy in dropbox - NOTE it will be removed after a few days

projectSum-etaf.xlsx

Shared with Dropbox

www.dropbox.com

 

[suri7891]

Hi there,

Thank you for help. Your 3D sum is working partially. But I might receive the different format every time. Therefore, this 3D sum does not solve my issue. I request you help on Indirect+Sumproduct formula for format changes.

Regards,
Suresh.K

 

[suri7891]

Hi Friends,

Please help me with the excel formula for my request. I need to consolidate the data from multiple sheets with the different sum range, multiple criteria.

Thanks,
Suresh.K

 

[suri7891]

Hi Friends,

I am a looking for excel formula for my issue. I need to consolidate data from different sheets into the my consolidate sheet. It has multiple criteria, multiple column sum range, and multiple sheets. It might be combined of Sumifs+sumproduct+Indirect formula for this issue. Because the format is different from one sheet to another.

Please help me.

Regards,
Suresh.K