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

 

[suri7891]

Here's an example that consolidates only two months. You'll need to add the remaining months to the list of months in Column I, and adjust the formula accordingly.

suri.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Resource Name	Project A	Project B	Project C	Project D	Project E			Sheet Names
2	A	7	3	3	6	10			Jan
3	B	5	7	4	7	9			Feb
4	C	3	7	4	6	6
5	D	7	5	6	6	10
6	E	9	7	7	5	5
7	F	4	9	7	5	9
8
Consolidation

Cell Formulas
Range	Formula
B2:F7	B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&$I$2:$I$3&"'!A2:A7"),$A2,OFFSET(INDIRECT("'"&$I$2:$I$3&"'!B2:F7"),0,MMULT(IF(T(OFFSET(INDIRECT("'"&$I$2:$I$3&"'!B1:F1"),0,COLUMN(INDIRECT("B:F"))-COLUMN(INDIRECT("B:B")),1,1))=B$1,COLUMN(INDIRECT("B:F"))-COLUMN(INDIRECT("B:B")),0),TRANSPOSE(COLUMN($B$1:$F$1))^0),,1)))

suri.xlsm
	A	B	C	D	E	F	G
1	Resource Name	Project C	Project A	Project E	Project B	Project D
2	E	4	4	4	5	3
3	B	2	4	5	2	4
4	C	3	2	2	3	5
5	A	2	5	5	2	1
6	D	5	2	5	4	1
7	F	4	2	4	4	3
8
Jan

suri.xlsm
	A	B	C	D	E	F	G
1	Resource Name	Project E	Project C	Project A	Project D	Project B
2	A	5	1	2	5	1
3	D	5	1	5	5	1
4	C	4	1	1	1	4
5	E	1	3	5	2	2
6	B	4	2	1	3	5
7	F	5	3	2	2	5
8
Feb

Hope this helps!

Hi there,

Thank you very much. The formula is exactly matching my requirement. Thanks for your help again.

Thanks,
Suresh.K

 

[smozgur]

Thank you very much. The formula is exactly matching my requirement.

The marked solution post has been changed accordingly.

@suri7891: In your future questions, please mark the post that answered your question as the solution to help future readers. No further action is required for this thread.

 

[Domenic]

Hi there,

Thank you very much. The formula is exactly matching my requirement. Thanks for your help again.

Thanks,
Suresh.K

That's great, I'm glad I could help, and thanks for your feedback.

Cheers!