Hi, I would like to make a sum of the last 3 (variable B7) columns with a value per row. The problem is that I want to exclude the rows without the value -.In the example below green is the result i would like to get from the sumThe light blue fields are the ones I want to use for the sum The fields with - are the ones I would like to exclude Can anyone help me with this formula?

ITTWS As anticipated by HansVogelaar here are two possible E365 solutions. In F2 and copy down:=SUM(TAKE(FILTER(B2:E2,B2:E2<>"-"),,-$B$7))or one that spills the results in one go:=BYROW(B2:E5,LAMBDA(a,SUM(TAKE(FILTER(a,a<>"-"),,-B7))))

Sum last columns with value only | Microsoft Community Hub

HansVogelaar

MVP

Aug 23, 2023

ITTWS

Others will undoubtedly come up with a clever formula using Excel 365-only functions.

Here is a custom VBA function:

Function SumLast3(rng As Range, n As Long) As Double
    Dim i As Long
    Dim m As Long
    Dim s As Double
    For i = rng.Count To 1 Step -1
        If IsNumeric(rng(i).Value) Then
            s = s + rng(i).Value
            m = m + 1
            If m = 3 Then Exit For
        End If
    Next i
    SumLast3 = s
End Function

Use like this in F2:

=SumLast3(B2:E2,$B$7)

Fill down.

(Added: a better name for the function would have been SumLastN)

Riny_van_Eekelen
Platinum Contributor
Aug 23, 2023
ITTWS As anticipated by HansVogelaar here are two possible E365 solutions.

In F2 and copy down:
=SUM(TAKE(FILTER(B2:E2,B2:E2<>"-"),,-$B$7))
or one that spills the results in one go:
=BYROW(B2:E5,LAMBDA(a,SUM(TAKE(FILTER(a,a<>"-"),,-B7))))
- ITTWS
  Copper Contributor
  Aug 23, 2023
  Riny_van_Eekelen
  
  Thanks. I think the first option would fit me best.
  
  But when i past the formula I get the error below...
  - HansVogelaar
    MVP
    Aug 23, 2023
    ITTWS
    
    If you use comma as decimal separator, replace all commas in the formula with semicolons ;
OliverScheurich
Gold Contributor
Aug 23, 2023
ITTWS
=SUM(INDEX(B2:E2,LARGE(IF(B2:E2<>"-",COLUMN(A1:D1)),MIN($B$7,COUNT(B2:E2)))):INDEX(B2:E2,LARGE(IF(B2:E2<>"-",COLUMN(A1:D1)),1)))
Another alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
SergeiBaklan
MVP
Aug 23, 2023
ITTWS

May I clarify, you'd like to sum last N columns, or last N values in the row excluding "-"?

Other words, if B7 is 2, what shall be in F2, 25 or 15?

Forum Discussion

Sum last columns with value only

Share

Resources