title: SumIF Using Table/Named Range Instead of Single Cell Criteria
body: <p>I have 2 sheets in a workbook (Sheet1, Sheet2).</p>
  
  <p>Sheet 2 contains a table (Named Table1) with 5 columns:</p>
  
  <ul>
  <li>Takeaways</li>
  <li>Household</li>
  <li>Clothing</li>
  <li>Fuel</li>
  <li>Groceries</li>
  </ul>
  
  <p>On sheet one, I have 2 columns: </p>
  
  <ul>
  <li>Expense Name</li>
  <li>Expense Total</li>
  </ul>
  
  <p>Now, what I am trying to do is:</p>
  
  <ol>
  <li>Set the range for the Expense Name (Range 1)</li>
  <li>Set the range for the Expense Total (Range 2)</li>
  <li>Compare Range 1 with the respective column in the table and only add up the values for matches</li>
  </ol>
  
  <p>For example, in Range 1 (B6:B16):</p>
  
  <ul>
  <li>BP</li>
  <li>Caltex</li>
  <li>McDonalds</li>
  <li>KFC</li>
  </ul>
  
  <p>In Range 2 (C6:C16):</p>
  
  <ul>
  <li>300</li>
  <li>400</li>
  <li>200</li>
  <li>150</li>
  </ul>
  
  <p>Now, all I want to do is add up the values for the Takeaways (McDonalds, KFC) and exclude anything that DOES NOT match the criteria.</p>
  
  <p>So my sum total will be all occurrences of Takeaways - provided they are listed in my table - 350 in this case.</p>
  
  <p>But I cannot seem to get the formula to work.</p>
  
  <p>I used these sources:</p>
  
  <p><a href="https://exceljet.net/excel-functions/excel-sumifs-function" rel="nofollow noreferrer">https://exceljet.net/excel-functions/excel-sumifs-function</a></p>
  
  <p><a href="https://stackoverflow.com/questions/40794638/selecting-a-specific-column-of-a-named-range-for-the-sumif-function">Selecting a Specific Column of a Named Range for the SUMIF Function</a></p>
  
  <p>and ended up with this formula:</p>
  
  <pre><code>=SUMIF($B$6:$B$16;Table1[Takeaways];C6:C16)
  </code></pre>
  
  <p>This source:</p>
  
  <p><a href="https://excelchamps.com/blog/sumif-sumifs-or-logic/" rel="nofollow noreferrer">https://excelchamps.com/blog/sumif-sumifs-or-logic/</a></p>
  
  <p>and ended up with this formula:</p>
  
  <pre><code>=SUM(SUMIFS(C6:C16;B6:B16;Table1[Takeaways]))
  </code></pre>
  
  <p>Both formulae return 0.</p>
  
  <p>BUT, with BOTH of them, if I change Table1[Takeaways] to "McDonalds", then it correctly identifies every occurrence of the word "McDonalds" in Range 1.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p><em>I have updated the formulae above to match the images below.</em></p>
  
  <p>This is the table that contains the references:</p>
  
  <p><a href="https://i.stack.imgur.com/EgnRh.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EgnRh.png" alt="enter image description here"></a></p>
  
  <p>This table contains the data:</p>
  
  <p><a href="https://i.stack.imgur.com/k8GOS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/k8GOS.png" alt="enter image description here"></a></p>
  
  <p><strong><em>Formula:</em></strong></p>
  
  <p>Cell C4 (Next to Takeaways): <code>=SUMIF($B$6:B$16;Table1[Takeaways];C6:C16)</code></p>
  
  <p>Cell C5 (Next to Fuel): <code>=SUM(SUMIFS(C6:C16;B6:B16;Table1[Fuel]))</code></p>
  
  <p>It appears that ONLY BP is being detected in the formula.</p>
  
  <p>This is a an output table when I use the formulae with a single cell reference and not a table or used range:</p>
  
  <p><a href="https://i.stack.imgur.com/48sqZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/48sqZ.png" alt="enter image description here"></a></p>
  
  <p><strong><em>Formula:</em></strong></p>
  
  <p>Cell F4 (Next to BP): <code>=SUMIF($B$6:B$16;"BP";C6:C16)</code></p>
  
  <p>Cell F5 (Next to Caltex): <code>=SUM(SUMIFS(C6:C16;B6:B16;"Caltex"))</code></p>
  
  <p>Cell F6 (Next to McDonalds): <code>=SUMIF($B$6:B$16;"McDonalds";C6:C16)</code></p>
  
  <p>Cell F7 (Next to KFC): <code>=SUM(SUMIFS(C6:C16;B6:B16;"KFC"))</code></p>