CODEWITHSUNDEEP
excel
cherrun
cherrun

Reputation: 2142

Get sum of range in one cell

I want to have a formula that calculates the sum of a given range.

Say I have 1,5 in cell A1. I want a formula to calculate 1+2+3+4+5. And maybe in a more advanced fashion, I would like to enter 1,5;6,3;1,4;... and have it calculate (1+2+3+4+5)+(6+5+4+3)+(1+2+3+4).

There won't be any negative numbers.

Is this somehow possible in Excel? For the range 6,3, it's probably easiest to get the lower number and count up to 6.

Upvotes: 0

Views: 919

Answers (3)

lori_m
lori_m

Reputation: 5567

For a worksheet function approach you could try one of the following:

  1. Basic case eg A1 = 1,5 or 6,3

    =SUMPRODUCT(ROW(INDIRECT(SUBSTITUTE(A1,",",":"))))

  2. General case eg A1 = 1,5;6,3;1,4.

    Define the name A =EVALUATE("{"&A1&"}") and then enter:

    =SUMPRODUCT(ABS(MMULT(A^2,{1;-1}))/2+A)/2

Update. A short VBA udf based on the method above...

Function SumSeries(Val As String)
SumSeries = Evaluate("SUM(ABS(MMULT({" & Val & "}^2,{1;-1}))/2+{" & Val & "})/2") 
End Function

Upvotes: 1

jrad
jrad

Reputation: 3190

Here's what I've come up with:

Sub addRange()
    Dim cell As Range
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("A:A"))
    Dim i As Integer
    i = 1
    Do While i <= count
        Dim low As Integer
        Dim high As Integer
        low = Mid(Range("A" & i), 1, Application.WorksheetFunction.Search(",", Range("A" & i)) - 1)
        high = Mid(Range("A" & i), Application.WorksheetFunction.Search(",", Range("A" & i)) + 1, Len(Range("A" & i)))
        If (low > high) Then
            Dim copy As Integer
            copy = low
            low = high
            high = copy
        End If
        Range("B" & i) = adder(low, high)
        i = i + 1
    Loop
End Sub

Function adder(low As Integer, high As Integer) As Integer
    Dim sum As Integer
    sum = 0
    Do While low <= high
        sum = sum + low
        low = low + 1
    Loop
    adder = sum
End Function

This assumes that you have values like 2,5, 6,4, 1,8, and others in cells A1:An. It doesn't work for values like 2,5;6,4 as you describe to be the more advanced function, but it gets the job done for the basic ones.

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27478

Here's a user-defined function that does what you want. It works for negatives as well. There is no error checking, e.g., for letters in the cell, more than one comma between semicolons (the first will generate a #VALUE error anyways).

Enter this function in a module in your workbook or in an addin:

Function AddSubstringProgressions(CellContent As String)
Dim Progressions As Variant
Dim Progression As Variant
Dim i As Long, j As Long
Dim Total As Long
Dim Stepwise As Long

Progressions = Split(CellContent, ";")
For i = LBound(Progressions) To UBound(Progressions)
    Progression = Split(Progressions(i), ",")
    If Val(Progression(LBound(Progression))) > Val(Progression(UBound(Progression))) Then
        Stepwise = -1
    Else
        Stepwise = 1
    End If
    For j = Progression(LBound(Progression)) To Progression(UBound(Progression)) Step Stepwise
       Total = Total + j
    Next j
Next i

AddSubstringProgressions = Total
End Function

Call it like this:

enter image description here

Upvotes: 3

Related Questions