Need Excel SUBTOTAL to use sums from differing columns conditionally

Question

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.

LOL That's as close as I've come to one of those in about 18 months...darned diet :) — David W, Jan 28 '13 at 15:16
Why not simply add another column `=IF(B2=0,C2,B2)`and then subtotal this instead? — Peter Albert, Jan 28 '13 at 15:21
The sample table used for illustration is extracted from a much larger spreadsheet which has a very specific format, and thus I don't have the option of adding a column. That's a very obvious consideration I should have mentioned in the original post - good catch. — David W, Jan 28 '13 at 15:23
Maybe here is the solution: http://stackoverflow.com/questions/759656/how-does-one-sum-only-those-rows-in-excel-not-filtered-out — Jüri Ruut, Jan 28 '13 at 15:26
Thanks @JüriRuut, but that solution does not address the conditional issue I need to satisfy here. Thanks nonetheless! — David W, Jan 28 '13 at 15:30
So you actually just want to sum columns B and column C because when you don't want the values from B they are zero so they wont affect the total. So =SUBTOTAL(9,B2:B13) + SUBTOTAL(9,C2:C13) — Dan, Jan 28 '13 at 15:34
@Dan Close, but not quite, because I do *not* want the Column C value involved if the corresponding Column B value is non-zero. A non-zero Column B value, if present, trumps anything in Column C. That formula adds *everything* from both columns, and thus doesn't honor that condition. The actual spreadsheet from which this extract was provided may have values in Column C when Column B is nonzero, so I should amend the problem statement to that effect. — David W, Jan 28 '13 at 15:38
=SUBTOTAL(9, SUMPRODUCT(--(B2:B13=0),C2:C13))+SUBTOTAL(9,B2:B13) ? — Dan, Jan 28 '13 at 15:42
Thanks again, @Dan, but that won't work, as SUBTOTAL is expecting a cell reference after the first argument, and SUMPRODUCT returns a value, and hence won't work. I sure appreciate your efforts, though! — David W, Jan 28 '13 at 15:47
@JuriRuut As I noted in the original post, I would prefer not to go that route for this solution, if possible. I won't entirely exclude it, but I'd prefer to exhaust the non-VBA options first. — David W, Jan 28 '13 at 15:54
Could this post possibly help you out? http://blog.excelgeek.com/2010/11/sumproduct-that-only-includes-visible.html — Dan, Jan 28 '13 at 16:40
Thanks, @DanielOtykier! I did see that very post last Friday, and I had hope that the per-cell treatment that construct allows might have afforded a solution, but alas, it didn't. I was hoping to amend that solution to use an IF that would have incorporated my test condition, but Excel balked at it. After several different permutations, I finally had to give up on that angle. — David W, Jan 28 '13 at 16:56
@David W - Yes, the method that Daniel points to should work for you - see my answer....... — barry houdini, Jan 28 '13 at 21:26

score 3 · Answer 1 · answered Jan 28 '13 at 18:21

3

I gave up and resigned to VBA-based UDF:

Function DifColSubTotal(Range1 As Range, Range2 As Range) As Single

  Dim c As Range
  Dim sum As Single
  Dim col_offset As Long

  col_offset = Range2.Column - Range1.Column

  For Each c In Range1
     If c.Height > 0 Then
       If ((c.Value = 0) Or (c.Value = "")) Then
           sum = sum + c.Offset(0, col_offset)
       Else
           sum = sum + c.Value
       End If
     End If
  Next
  DifColSubTotal = sum
End Function

The result is here:

enter image description here

answered Jan 28 '13 at 18:21

Jüri Ruut

2,500
17
20

I very much appreciate your time and effort. Thank you! – David W Jan 28 '13 at 18:33
I suppose for 1-cell option that's the only possible solution, formulas are helpless for the case. +1! – Peter L. Jan 28 '13 at 19:06

barry houdini · Accepted Answer · 2013-01-29T14:42:50.670

3

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

edited Jan 29 '13 at 14:42

answered Jan 28 '13 at 21:15

barry houdini

45,615
8
63
81

This appears to work! I saw the referenced site before and experimented extensively with the ROW/OFFSET trick, trying to incorporate an IF within the expression to leverage the per-cell evaluation, but Excel continually complained about it. Turns out I was just putting the IF in the wrong place!! Thanks very much. This looks like precisely what I was hoping for. – David W Jan 29 '13 at 00:46
No problem - I also added a version closer to your original which doesn't require "array entry"...... – barry houdini Jan 29 '13 at 14:42

Need Excel SUBTOTAL to use sums from differing columns conditionally

2 Answers2