title: Excel Conditional SUMPRODUCT
body: <p>Consider the following:</p>
  
  <pre><code>    A    B    C
  ----------------
  1   a    c    1
  2   b    f    2
  3   a    c    3
  ...
  6   a    c    4
  </code></pre>
  
  <p>The goal here is to use a conditional SUMPRODUCT so that when the values in row 6 match values in their respective column, the SUMPRODUCT is computed. Here is the interesting thing.</p>
  
  <p>When using:</p>
  
  <pre><code>=SUMPRODUCT((A3:A5=A1),(B3:B5=B1),C3:C5)
  </code></pre>
  
  <p>the input arguments are evaluated as:</p>
  
  <pre><code>=SUMPRODUCT({TRUE, FALSE, TRUE},{TRUE, FALSE, TRUE},{1, 2, 3})
  </code></pre>
  
  <p>but the result is 0.</p>
  
  <p>Now when casting the boolean TRUEs and FALSEs to their 1 and 0 counterparts 'before' evaluation of the formula with a mathematical operator, the proper result populates:</p>
  
  <pre><code>=SUMPRODUCT(--(A3:A5=A1),--(B3:B5=B1),C3:C5)
  </code></pre>
  
  <p>or</p>
  
  <pre><code>=SUMPRODUCT(ABS(A3:A5=A1),ABS(B3:B5=B1),C3:C5)
  </code></pre>
  
  <p>or</p>
  
  <pre><code>=SUMPRODUCT((A3:A5=A1)^2,(B3:B5=B1)^2,C3:C5)
  </code></pre>
  
  <p>etc..., </p>
  
  <p>eventually evaluates to 1s and 0s once the mathematical operator is applied,</p>
  
  <pre><code>=SUMPRODUCT({1, 0, 1},{1, 0, 1},{1, 2, 3})
  </code></pre>
  
  <p>and we get the proper value.</p>
  
  <p>We having been racking our minds over Excel can't seem to properly evaluate it's own inputs. We think there is a line of code missing the the SUMPRODUCT compiled code that converts the TRUEs and FALSEs to 1 and 0 and error trapping simply returns a 0.</p>
  
  <p>Any thoughts?</p>