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]

Hi Friends,

Please help me on my query.

Regards,
Suresh.K

 

[fjns]

Hi, see the linked file for a possible solution...
Consolidate.xlsx

 

[suri7891]

Hi there,

Thank you for your effort. Your formula is really good and helping me for the constant data. But my requirement is different. I get these kind of sheets every week with the different formats. Then, this formula is very difficult to change all the time.

I request you to help me Sumproduct+Indirect formula with multiple criteria.

Thanks for your support again.

Thanks,
Suresh.K

 

[fjns]

Hi,
I would like to see the different formats.
Best regards,
Janos

 

[suri7891]

Hi there,

Thanks for your support. I am unable to attach the excel sheet. Please find the screenshot of each sheets which I get it. All the sheets have different formats. And I need to consolidate the information in the consolidate sheet . It has 2 criteria, one is Project A, B, E etc in the column, and another one is A, B, C etc in the rows in different formats. Therefore, I need the sumproduct+Indirect with multiple criteria formula to consolidate everything.

Please help.

Thanks,
Suresh.K

 

[fjns]

Hi,
my formula is irrespective of the order of columns and rows.
In Feb-sheet the column F is named A. This column should be renamed Project A.
(Sorry, my English is too weak.)

 

[fjns]

Hi, the table for 12 months is ready...
Consolidate12.xlsx

 

[Fluff]

@fjns please post your solution to the thread, not just a link to a workbook, as per Rule#4 Thanks

 

[fjns]

@Fluff Unfortunately, XL2BB is not allowed on my workplece machine.

 

[Gloffo]

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))