title: Need Excel SUBTOTAL to use sums from differing columns conditionally
body: <p>I have a fairly simple Excel 2007 worksheet with three columns; a name (Column A), Value1 (Column B), and Value2 (Column C). This sheet will often be auto-filtered by the Name column.</p>
  
  <p>At the bottom of the 2nd column, I want to perform a conditional SUBTOTAL of Column B, honoring any filters, with the following rule: If for a given row Column B is 0, use the value for that row from Column C, <em>otherwise, column C should be ignored.</em></p>
  
  <pre><code>      A         B         C       
  1   Name     Value1    Value2
  2  Bob         100         6   &lt;-- use 100, ignore 6 in Col C because B is non-zero
  3  Bob         200             &lt;-- use 200
  4  Bob           0        50   &lt;-- Col B=0, use C, thus 50 (and so on)
  5  Bob         300
  6  Ralph        10
  7  Ralph        20         1   &lt;-- use 20 from col B, ignore col 6.
  8  Ralph         0        60
  9  Ralph        50
  10 Mary       1000
  11 Mary       1200
  12 Mary          0       250
  13 Mary       1040       
  14 Subtotal   4280
  </code></pre>
  
  <p>Now, I can get the total <em>value</em> I want with the formula:</p>
  
  <p>=SUMPRODUCT(--(B2:B13=0),C2:C13)+SUBTOTAL(9,B2:B13)</p>
  
  <p>But the use of SUMPRODUCT prevents it from honoring the hidden/filtered cells requirement, eg filtering out Mary doesn't cause the total to go down by 3690. And SUBTOTAL can't use the (B2:B13=0) array reference. So a good part of my brain is trying to tell me I can get darned close, but really can't get there. But I'm (too?) stubborn to give up that quickly :)</p>
  
  <p>Part of me is trying to say this can't be done directly without delving into VBA, which I would prefer to avoid if possible for this solution. I thought it worthwhile to solicit some other eyeballs on the problem in an effort to point out a (likely very simple) solution I just don't see.</p>
  
  <p><strong>EDIT 1:</strong> One obvious solution I should have pointed out was simply to add a fourth column with a simple IF expression, and then SUBTOTAL that. That certainly works, but I cannot adapt that into this situation. The table illustrated here is merely a representative extract from a much larger, highly structured worksheet such that the addition of an arbitrary column is not an option. </p>
  
  <p><strong>EDIT 2:</strong> The sample data I provided allowed for a simple inference that a straight SUBTOTAL of all columns would solve the problem, but the real-world data from which this data was extracted may include values for Column C even when Column B is non-zero. In that case, Column C must be ignored - column B, if present, always trumps. I've amended the original data table accordingly.</p>