SOLVED

Excel sum problem

Copper Contributor

Hi guys, I need a help, I want to sum cells in same columns like in picture which I added. Left side is done manually  by me with auto sum function and is it possible to sum automatically all sums at the same time?

Please share your knowledge.

Thank you in advance. sum.png

6 Replies

@Lomsona 

Use AutoSum to sum numbers

If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.

When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.

 

 

Thank you, there are a lot of data in one column and its take a lot of time to sum with AutoSum each of them, I`m interesting if there is some formula or function to do it with one click and not to sum each one manually.

@Lomsona 

Try this macro, and please test thoroughly. It doesn't handle all situations, so let me know if and how it fails.

Sub CreateSum()
    Dim rngConstants As Range
    Dim rngFormulas As Range
    Dim rngTotal As Range
    Dim rng As Range
    On Error Resume Next
    Set rngConstants = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
    Set rngFormulas = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If rngConstants Is Nothing Then
        If rngFormulas Is Nothing Then
            Exit Sub
        Else
            Set rngTotal = rngFormulas
        End If
    ElseIf rngFormulas Is Nothing Then
        Set rngTotal = rngConstants
    Else
        Set rngTotal = Union(rngConstants, rngFormulas)
    End If
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each rng In rngTotal.Areas
        If Not rng.Cells(rng.Count).HasFormula Then
            rng.Cells(1).Offset(rng.Rows.Count).Formula = "=SUM(" & rng.Address & ")"
        End If
    Next rng
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

Thank you, and I`ll give you feedback.
best response confirmed by Lomsona (Copper Contributor)
Solution

@Lomsona Alternatively, select the entire column E. Press F5, then Special... and Constants and OK. Now press AutoSum.

 

 

Thank you very much, that is what I was watching for.
1 best response

Accepted Solutions
best response confirmed by Lomsona (Copper Contributor)
Solution

@Lomsona Alternatively, select the entire column E. Press F5, then Special... and Constants and OK. Now press AutoSum.

 

 

View solution in original post