Thread: How to incorporate an if then else function to a sum formula

I have the formula

=sum(B5:B3)

If zeros appear in any cell in column a I need the corresponding cell in column C to show a zero and the calculation to include the next cell in column A that has a value greater than zero.

I have attached a sample with a better description.

I thought this was a good opportunity for me to practice writing custom worksheet functions, so I created one for you called Last3() and installed it into your sample sheet. Here's the code:

Option Explicit
Private Function Last3(Rng As Range)
'Jerry Beaucaire (6/19/2009)
'Finds the last 3 non-zero values in first column, sums values from second column
Dim MyArray, Count As Long, counter As Long, i As Long
MyArray = Rng

    If UBound(MyArray) < 3 Then
        Last3 = "Low Range"
        Exit Function
    End If

    For i = UBound(MyArray) To 1 Step -1
        If Rng(i, 1) <> 0 Then
            Count = Count + MyArray(i, 2)
            counter = counter + 1
            If counter = 3 Then Exit For
        End If
    Next i

Last3 = Count
End Function

How to install the UDF:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The function works like this, from cell C5 in your sample workbook:
=LAST3($A$3:B5))

It's a two-column range at least 3 rows deep. You'll get a "Low Range" error if you put too few rows in the range.

I nested this into your "make sure column A isn't 0" and got this final formula for C5, copied all the way down the column:

=IF(A5=0, 0, LAST3($A$3:B5))

As it's already in VBA, here is a formula that works

=IF(A11=0,0,SUM(INDEX($B$1:$B11,LARGE(($A$2:$A11<>0)*ROW($A$2:$A11),1),0),INDEX($B$1:$B11,LARGE(($A$2:$A11<>0)*ROW($A$2:$A11),2),0),INDEX($B$1:$B11,LARGE(($A$2:$A11<>0)*ROW($A$2:$A11),3),0)))

Please note that this is an array formula and needs to be entered with CTRL-SHIFT-ENTER

I am also uploading up your spreadsheet so you can see the formula (In column D)

Hope that helps..
John

No, the UDF should be much quicker than array formulas. That's why I went that route. I imagined your project was large.

That is a sweet formula, though, Kraljb.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Here you go, added a test at the end so you only get a value if the COUNTER is 3. With this change, you can now put the formula in C3 and copy down.

Option Explicit
Private Function Last3(Rng As Range)
'Jerry Beaucaire (6/19/2009)
'finds the last 3 non-zero values in first column, sums values from second column
Dim MyArray, Count As Long, counter As Long, i As Long
MyArray = Rng

    For i = UBound(MyArray) To 1 Step -1
        If Rng(i, 1) <> 0 Then
            Count = Count + MyArray(i, 2)
            counter = counter + 1
            If counter = 3 Then Exit For
        End If
    Next i
    
    If counter < 3 Then
        Last3 = 0
    Else
        Last3 = Count
    End If
End Function

For what it's worth, here's another formula approach...

C3, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=IF(COUNTIF($A$3:A3,">0")>=3,IF(A3=0,0,SUM(IF(ROW($A$3:A3)>=LARGE(IF($A$3:A3,ROW($A$3:A3)),3),IF($A$ 3:A3,$B$3:B3)))),"")

There's always something new to learn. I've only just started writing some custom functions, yours was the first I wrote for someone else...or maybe the second.

Anyway, always always use the forums. I've used them for years and am surprised every week by the new stuff I learn. Whole macros I used to use have been replaced by simple worksheet formulas. I recently wrote a complicated macro that someone else showed a way to do it with about 3 lines of code.

Hehe, I love it.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

If you have a followup question, be sure to post it here along with a relevant copy of your current workbook.