title: Inconsistent SUMPRODUCT return value
body: <p>What the heck is going on here? Is this a bug? </p>
  
  <p>In the example below, this formula is meant to count the number of apples that aren't made of plastic. </p>
  
  <pre><code>=SUMPRODUCT(--(B4=IF(NOT($C$4:$C$11),$B$4:$B$11)))
  </code></pre>
  
  <p>It is supposed to return 1. Why is it returning 2?</p>
  
  <p><img src="https://i.stack.imgur.com/j96YC.png" alt="enter image description here"></p>
  
  <p>When I manually evaluate the array inside <code>SUMPRODUCT</code> using <kbd>F9</kbd>, I can see there's only one 1 there; when I then press <kbd>Enter</kbd>, it returns 1, which is what I expected from the start. </p>
  
  <p><img src="https://i.stack.imgur.com/ptvVr.png" alt="enter image description here"></p>
  
  <p><img src="https://i.stack.imgur.com/ibJ9G.png" alt="enter image description here"></p>
  
  <p>The best part: If I copy the exact same original formula to other cells (not dragged -- it has the exact same cell references), then it returns 0 or a <code>#VALUE!</code> error. Why would the result change like that?</p>
  
  <p><img src="https://i.stack.imgur.com/hOT5U.png" alt="enter image description here"></p>
  
  <p>Test data for your copy-pasting pleasure:</p>
  
  <pre><code>Fruit   Made of plastic
  apple   FALSE
  banana  FALSE
  plum    FALSE
  pear    TRUE
  apple   TRUE
  banana  FALSE
  plum    TRUE
  pear    TRUE
  </code></pre>