title: How to use SUMIFS and SUMPRODUCT function **with a multiplier column** for summing values if criteria range exists in a range?
body: <p>This question refers to <a href="https://stackoverflow.com/questions/25805571/how-to-use-sumifs-function-for-summing-values-if-criteria-range-exists-in-a-rang">this Stack Overflow question</a>.</p>
  
  <p>This is the original question:</p>
  
  <pre><code>A   B    C 
  a   3    d 
  b   1    a 
  c   8    e 
  d   5    
  </code></pre>
  
  <blockquote>
    <p>I want to use <code>SUMIFS</code> function, sum range is <code>B1:B4</code>, if the corresponding value in column <code>A</code> exists in column <code>C</code>, sum the values in column <code>B</code>, in this case the sum will be <code>3 + 5 = 8</code>.</p>
  </blockquote>
  
  <p>The answer turned out to be:</p>
  
  <pre><code>=SUMPRODUCT(SUMIF(A1:A4,C1:C3,B1:B4)) 
  </code></pre>
  
  <hr>
  
  <p>I would like to create the same result, but with a multiplier on one of the ranges. </p>
  
  <p>Here's my question:</p>
  
  <pre><code>A   B   C   D
  a   3   d   3
  b   1   a   1
  c   8   e   1
  d   5   d   3
  </code></pre>
  
  <p>The only thing I would like to do differently is add a range <code>D1:D3</code> that acts as a multiplier. In this case, the sum I want would be <code>(3x3)+(5x3)= 24</code>.</p>
  
  <p>One challenge is that the ranges are different sizes in the "Test Data" google sheet linked below. The "Test Data" sheet is an example of how I would like to total daily calories based on the number of servings of items that I eat as dictated by the "multiplier column." </p>
  
  <p>I would like the numbers in <code>C2:C27</code> to be a multiplier and factored into the total in <code>D36</code>. </p>
  
  <p>For example, if I ate 2 servings of Beef, then 2 servings worth of Beef's ​calories would be displayed in the daily totals below the data. Currently, using the formula =SUMPRODUCT(SUMIF(Meal_Items!$A$2:$A$100,$D$2:$D$28,Meal_Items!$B$2:$B$100)), only 1 serving of beef is weighted.</p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/1Vs380_PLwCT2wBdeyQ2regQwWWIQZKFOqk788rYr3xI/edit?usp=sharing" rel="nofollow noreferrer">Test Data</a></p>
  
  <p>See linked Test Data set above</p>