Sumifs formula for multiple sheets

[Loukas Char]

Hi all,

I have the following topic that I cannot take a step further:

I need to summarize data from 3 tabs into a table based on multiple criteria and as it can be seen below I have created 3 different sumifs. The index matches the month, and then follow the 2 criteria.
My goal is to reduce these 3 lines of code (which correspond to each tab) and my idea is to use a named range which would include the name of the tabs (i.e. FC P7 DE, FC P7 DK, FC P7 UK) To make things more complicated, the named range will change each month to the following ( I will be inserting the new month tabs manually).

=SUMIFS(INDEX('FC P7 DE'!$D$4:$O$30;;MATCH('FC P7 DE'!H$3;'FC P7 DE'!$D$3:$O$3;0));'FC P7 DE'!$A$4:$A$30;$A10;'FC P7 DE'!$C$4:$C$30;$B10)+
SUMIFS(INDEX('FC P7 DK'!$D$4:$O$30;;MATCH('FC P7 DK'!H$3;'FC P7 DK'!$D$3:$O$3;0));'FC P7 DK'!$A$4:$A$30;$A10;'FC P7 DK'!$C$4:$C$30;$B10)+
SUMIFS(INDEX('FC P7 UK'!$D$4:$O$30;;MATCH('FC P7 UK'!H$3;'FC P7 UK'!$D$3:$O$3;0));'FC P7 UK'!$A$4:$A$30;$A10;'FC P7 UK'!$C$4:$C$30;$B10)

Any help highly appreciated.

Best regards,
Loukas

 

[Chrisdontm]

This may send you in the right direction;

To retrieve the sheet name use: =CELL("filename")

That will give you the whole path, but you could use: RIGHT to extract it, and then use it in an INDIRECT formula so you wouldn't have to change the formula every Month....

I hope that is enough to get you there...

 

[Loukas Char]

Hi Chrisdontm.

Thanks for your reply. Maybe I was not so clear:
The code above is in a Total tab (where I am summing data from the 3 tabs FC P7 DE, FC P7 DK, FC P7 UK). Instead of having 3 lines I would like to reduce it, to say by 1, by iterating through the names of these tabs in 3 cells (by refering to each one of them within the formula somehow).

So the formula should like something like:

=SUMIFS(INDEX(refering to the range of tabs$D$4:$O$30;;MATCH(refering to the range of tabsH$3;refering to the range of tabs$D$3:$O$3;0));refering to the range of tabs$A$4:$A$30;$A10;refering to the range of tabs$C$4:$C$30;$B10)

Does it make more sense now?