Search code examples
excelsubtotal

Need Excel SUBTOTAL to use sums from differing columns conditionally

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.

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, otherwise, column C should be ignored.

      A         B         C       
1   Name     Value1    Value2
2  Bob         100         6   <-- use 100, ignore 6 in Col C because B is non-zero
3  Bob         200             <-- use 200
4  Bob           0        50   <-- Col B=0, use C, thus 50 (and so on)
5  Bob         300
6  Ralph        10
7  Ralph        20         1   <-- 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

Now, I can get the total value I want with the formula:

=SUMPRODUCT(--(B2:B13=0),C2:C13)+SUBTOTAL(9,B2:B13)

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 :)

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.

EDIT 1: 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.

EDIT 2: 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.

Solution

  • To do this with formulas the trick is to use OFFSET to return an "array of ranges", each one a single cell, and we can then use SUBTOTAL to query each cell individually, i.e. using this "array formula"

    Edit - as per Daniel Otykier's comment - didn't see that originally.....

    =SUM(IF(SUBTOTAL(2,OFFSET(B2,ROW(B2:B13)-ROW(B2),0)),IF(B2:B13=0,C2:C13,B2:B13)))

    confirmed with CTRL+SHIFT+ENTER

    Assumes that B2:B13 is fully populated as per example given

    Edit: you can also use this version which doesn't require "array entry"

    =SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C13)-ROW(C2),0)),(B2:B13=0)+0)+SUBTOTAL(9,B2:B13)

    This usage of SUBTOTAL/OFFSET was developed by Laurent Longre - see here