New Contributor

# Looking for Formula help please!

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.

3 Replies

# Re: Looking for Formula help please!

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

# Re: Looking for Formula help please!

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

# Re: Looking for Formula help please!

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