title: Excel sumproduct across multiple columns on multiple sheets with multiple criteria
body: <p>I have 12 worksheets named Jan 2022 through Dec 2022. On each worksheet column B contains workgroups, columns E:J contain estimate numbers, and row 2 contains the work week.</p>
  <p>On the summary worksheet I need to show the estimate for each work group for each work week. The summary worksheet is a large table, with column A containing the work groups and row 1 containing the work weeks. Cells B2:BB25 contain the sums of the estimates for the corresponding work group and work week.</p>
  <p>I have the names of the 12 worksheets listed in a named range called SheetList.</p>
  <p>I'll discuss the formula I have in B2.</p>
  <p>Current Solution:
  <code>=SUMIFS('Jan 2022'!E:E,'Jan 2022'!$B:$B,$A2)</code></p>
  <p><em>this gives me the correct number, but I have to manually align each formula with each work week on it's respective sheets, sometimes having to add 2 sumifs together when a work week overlaps the change of month.</em></p>
  <p>I'm trying to develop a solution that doesn't require manual assignment of columns for each work week throughout the year. It's an error-likely manual process.</p>
  <p>Here is what I've tried:</p>
  <ol>
  <li><p><code>=SUMPRODUCT(SUMIFS(INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$E:$E&quot;),INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$B:$B&quot;),$A2))</code>
  <em>this sums for a single work group across all months, but i can't figure out how to add in another criteria where <code>INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$e$2:$j$2&quot;)=B$1</code> so that it isolates the particular work week. Also, this would require 6 <code>SUMPRODUCT</code> formulas in each cell.</em></p>
  </li>
  <li><p><code>=SUMPRODUCT((INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$E$5:$J$1000&quot;))*(INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;E$2:$J$2&quot;)=B$1)*(INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$B$5:$B$1000&quot;)=$A2))</code>
  <em>this returns a <code>#VALUE</code> error. i think it's because i can't use <code>INDIRECT</code> in a <code>SUMPRODUCT</code> without a <code>SUMIFS</code></em></p>
  </li>
  <li><p><code>=SUMPRODUCT(N(INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$E$5:$J$1000&quot;))*(N(INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$E$2:$J$2&quot;))=B$1)*(N(INDIRECT(&quot;'&quot;&amp;SheetList[Sheet Names]&amp;&quot;'!&quot;&amp;&quot;$B$5:$B$1000&quot;))=$A2))</code>
  <em>this returns &quot;0&quot;</em></p>
  </li>
  </ol>
  <p>here's a link to the example file <a href="https://docs.google.com/spreadsheets/d/1zOHV0Mih20se283DMHD7-oXO8enh5tTD/edit?usp=sharing&amp;ouid=105924493725115064677&amp;rtpof=true&amp;sd=true" rel="nofollow noreferrer">2022 Monthly Estimates</a></p>