Aug 27 2022 03:26 AM
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.
Aug 27 2022 03:37 AM
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.
Aug 27 2022 03:45 AM
Aug 27 2022 04:56 AM
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
Aug 27 2022 05:08 AM
Solution@Lomsona Alternatively, select the entire column E. Press F5, then Special... and Constants and OK. Now press AutoSum.
Aug 27 2022 05:08 AM
Solution@Lomsona Alternatively, select the entire column E. Press F5, then Special... and Constants and OK. Now press AutoSum.