title: Excel VBA sum(sumifs) within sumproduct only works with F9
body: <p>Situation:</p>
  
  <pre><code>Row 1: Variable | Margin | Sales
  Row 2: banana   | 2      | 10
  Row 3: apple    | 5      | 20
  Row 4: apple    | 10     | 20
  </code></pre>
  
  <p>"Variable" sits in column A, row 1.</p>
  
  <p>Sales = name range for variable column i.e. A2:A4
  Fruit = name range for "apple" and "banana" i.e. {apple;banana}</p>
  
  <p>I have the above table and what to find the weighted average Margin. The weighting is based on sales.</p>
  
  <p>Formula used: <code>=SUMPRODUCT(B2:B4,C2:C4/(SUM(SUMIFS(C2:C4,sales,fruit))))</code></p>
  
  <p>Problem: </p>
  
  <p>When I use this formula I get #DIV/0!
  If i step into the formula and press F9 I get 6.40 which is the correct answer.</p>
  
  <p>Could someone please tell me what I am doing wrong? How can I get 6.40 without stepping into the formula with F9.</p>
  
  <p>Note:</p>
  
  <p>I would like to avoid using this formula: <code>=SUMPRODUCT(B2:B4,C2:C4/(SUM(SUMIFS(C2:C4,sales,{"apple","banana"}))))</code>
  The range of fruit included in the calculation is dynamic. Sometimes I'd like to include only banana, sometimes only apple and I could make it dynamic by changing the cells within the fruit named range.</p>