Looking for Formula help please!

Copper Contributor

Looking for assistance with a formula I had working great but it will not carry over onto multiple sheets. 


Two different formulas require the same multi-sheet function to work.


I have essentially a table of name references in "totals sheet" with cells D4-39 that link a name.  Sheets 1-31 are finding that name on whatever row it appears at as it can be sorted into different rows on any given sheet.  


Formula 1 needs to return sums of 2 different columns across those sheets.  E and F.  It works for sheet 1 where I set it up but won't work adding sheet references with eg. '01:31'


=SUMPRODUCT(('01'!A3:A53=D4)*'01'!E3:F53)  This works for sheet 1 as is but can't figure out multiple sheets. 


Formula 2 is similar but is counting the number of times a table reference "assignment" appears for an individual in the list at "Totals Page" cell D4-D39.  


=COUNTIFS('01'!$A$3:$A$52,$D$4,'01'!$C$3:$C$52,$F$3)  - again works great in sheet 1 but can't figure out how to reference sheets 1-31.   Tried every combination of '01:31' in the formula. 


Thanks, please help me out if you can.



3 Replies


Depends on which Excel you are. If on latest 265 that could be done easier, otherwise idea is here https://exceljet.net/formulas/sum-across-multiple-worksheets-with-criteria 

2019 Pro plus.  Your link makes some sense but still not working right.  Now I'm trying to figure out name ranging sheets. @Sergei Baklan 

=SUMPRODUCT(SUMIF(INDIRECT(" ' " &1:31& " ' ! " & "a3:a52"),D13,INDIRECT(" ' " &1:31& " ' ! " & "e3:f52")))