Sumproduct multiple sheets and multiple criterias

[jmavec]

In excel 2010 I’m using formula <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=SUMPRODUCT(--('Sheet1'!$D$1:$D$1027=$B23);--('Sheet1'!$C$1:$C$1027=L$2);('Sheet1'!$A$1:$A$1027))<o></o>
to get information from range A1:A1027 on Sheet1, if two criterion on current page are fulfilled. First criterion is that cell in range of D1:D1027 on Sheet1 matches cell in B column on current page and second criterion is that cell in the same row from range C1:C1027 on Sheet1 matches L2 cell on current pate. This formula is working fine on one page, however I would like to get formula working over the same range of cells (columns) on 20 Sheets at the same time and wouldn’t like to copy paste the same formula and summarize. Is there the way to do it, I tried with INDIRECT option but not success there.<o></o>
<o> </o>
Than you<o></o>
<o> </o>
Jože<o></o>

 

[Domenic]

First, list your sheets in a range of cells, let's say G1:G20. Then, try...


=SUMPRODUCT(SUMIFS(INDIRECT("'"&$G$1:$G$20&"'!A1:A1027");INDIRECT("'"&$G$1:$G$20&"'!D1:D1027");$B23;INDIRECT("'"&$G$1:$G$20&"'!C1:C1027");L$2))

 

[jmavec]

Perfect. It is working great, and it is even working with full columns, I don’t need to define range of cells and I added additional criterion. Now it looks like this:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&menu_sheet_list&"'!A:A");INDIRECT("'"&menu_sheet_list&"'!D:D");$B3;INDIRECT("'"&menu_sheet_list&"'!C:C");AA$2;INDIRECT("'"&menu_sheet_list&"'!B:B");$Z$1))
where menu_sheet_list defines the range of sheets.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thank you Domenic.<o></o>

 

[Domenic]

You're very welcome! Thanks for the feedback!

 

[jamilm]

i was going through the SUMPRODUCT formulas . i found this code very useful.

thanks Domenic.

First, list your sheets in a range of cells, let's say G1:G20. Then, try...


=SUMPRODUCT(SUMIFS(INDIRECT("'"&$G$1:$G$20&"'!A1:A1027");INDIRECT("'"&$G$1:$G$20&"'!D1:D1027");$B23;INDIRECT("'"&$G$1:$G$20&"'!C1:C1027");L$2))