Excel SUBTOTAL needs to use sums from different columns conditionally
I have a fairly simple Excel 2007 sheet with three columns; name (Column A), Value1 (Column B), and Value2 (Column C). This sheet is often auto-filtered by the Name column.
At the bottom of the second column, I want to conditionally SUBTOTAL column B respecting any filters with the following rule: if for a given row, column B is 0, then 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) + TOTAL (9, B2: B13)
But using SUMPRODUCT does not allow it to comply with the hidden / filtered cells requirement, for example, filtering Mary does not cause the total to decrease by 3690. AND SUBTOTAL cannot use an array (B2: B13 = 0) Help. So a good part of my brain is trying to tell me that I can get the darn close, but I really can't get there. But I am (too?) Stubborn to give it up quickly :)
Part of me is trying to say that this cannot be done directly without delving into VBA, which I would rather avoid if possible for this solution. I thought it might be worth pondering over the other issues in this problem to point out a (probably very simple) solution that I just can't see.
EDIT 1: One of the obvious solutions I should have noticed was to simply add a fourth column with a simple IF statement and then SUBTOTAL. This certainly works, but I can't seem to fit it in this situation. The table illustrated here is just a representative extract from a much larger, highly structured sheet, so adding an arbitrary column is not an option.
EDIT 2: The sample data presented made it easy to conclude that direct SUBTOTAL of all columns would solve the problem, but the real world data from which this data was retrieved might include values for column C even if column B is nonzero. In this case, Column C should be ignored - Column B, if present, always trumps. I modified the original data table accordingly.
source to share
To do this, trick it is to use OFFSET to return an "array of ranges", each of which is one cell, and then we can use SUBTOTAL to query each cell separately, ie. using this "array formula"
Edit - as per Daniel Otykir'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 CTRL+SHIFT+ENTER
It is assumed to be B2:B13
completely filled as shown in the example
Edit: you can also use this version which does not require "writing an array"
=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C13)-ROW(C2),0)),(B2:B13=0)+0)+SUBTOTAL(9,B2:B13)
This usage SUBTOTAL/OFFSET
was developed by Laurent Longre - see here
source to share
I gave up and gave up on VBA based UDFs:
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:
source to share