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

 

[Fluff]

Unfortunately, XL2BB is not allowed on my workplece machine.

That does not prevent you from posting the formulae you used.

 

[fjns]

@Fluff I apologize for the shortcoming. You are absolutely right, after that I will write my comments with formulas.
After my solution a much simpler solution from Gloffo arrived. So it's better that my complicated formula is not visible in this thread.
(Sorry, my English is too weak.)

 

[suri7891]

The easiest way would be to use power query to grab each table, then combining all months and generate a new combined table.

If you wanted to use a formula, I don't get why you need to use indirect. I would just make a normal sumproduct for january, adding a new for february and so forth. That way, the only thing you need to do each month is to add one more copy of the formula, but with the new month added. Like this.

=SUMPRODUCT(($A3=Jan!$A$2:$A$7)*(B$1=Jan!$B$1:$F$1)*(Jan!$B$2:$F$7))+SUMPRODUCT(($A3=Feb!$A$2:$A$7)*(B$1=Feb!$B$1:$F$1)*(Feb!$B$2:$F$7))

Hi Gloffo,

Actually, I have given the sample of my requirement. My raw data is always jumble. It has multiple columns and rows. The new columns and rows will be added regularly. Therefore, power query does not work. Therefore, I need the sumproduct+Indirect formula to resolve this issue.

I tried sumproduct+Indirect (with one condition). But I need to apply this for multiple condition. Please help.

Thanks,
Suresh.K

 

[Gloffo]

Hi Gloffo,

Actually, I have given the sample of my requirement. My raw data is always jumble. It has multiple columns and rows. The new columns and rows will be added regularly. Therefore, power query does not work. Therefore, I need the sumproduct+Indirect formula to resolve this issue.

I tried sumproduct+Indirect (with one condition). But I need to apply this for multiple condition. Please help.

Thanks,
Suresh.K

But my formula sums all columns in the table, regardless of order or duplicates. (Of course the format and spellings need to be the same in the names for the columns and rows. ) The only thing you need to do is to make sure you cover the entire table for each sheet.

 

[suri7891]

But my formula sums all columns in the table, regardless of order or duplicates. (Of course the format and spellings need to be the same in the names for the columns and rows. ) The only thing you need to do is to make sure you cover the entire table for each sheet.

Hi Gloffo,

Thanks for the explanation and efforts. I really appreciate it. But I am looking for a standard formula. Because, I get new sheets every week. Then, it is very difficult to change the formula every time.

I would be very helpful if you advise me sumproduct+indirect with multiple criteria formula.

Thanks,
Suresh.K

 

[suri7891]

Hi Friends,

I tried this password for my issue to resolve it. It includes sumproduct+sumifs+Indirect. But I am getting # Value error. I need this sumproduct and Indirect with multiple conditions. Please help me.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Calculation!$A$4:$A$5&"'!"&"G6:BQ138"),INDIRECT("'"&Calculation!$A$4:$A$5&"'!"&"A6:A138"),Sheet1!$A2)*(INDIRECT("'"&Calculation!$A$4:$A$5&"'!"&"G6:BQ138"),INDIRECT("'"&Calculation!$A$4:$A$5&"'!"&"G3:BQ3"),Sheet1!$B2))

Thanks,
Suresh.K

 

[suri7891]

Hi Friends,

I tried this password for my issue to resolve it. It includes sumproduct+sumifs+Indirect. But I am getting # Value error. I need this sumproduct and Indirect with multiple conditions. Please help me.

Regards,
Suresh.K

 

[ngochien251088]

I have the same issue with yours. I am trying to find out the standard fomular with multiple criterias ( columns and rows) and in multiple sheets. Awaiting for all of you guys help.
Thank you so much.

 

[Domenic]

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!

 

[ngochien251088]

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!

Thank you so much for your help. I will check and let you know if I have any more querries. Wish you all the best.