Home » Excel Formulas » How to Sum Selected Cells in Excel (4 Easy Methods)

How to Sum Selected Cells in Excel (4 Easy Methods)

Avatar photo
Written by Saquib Ahmad Shuvo
Last updated: Jun 25, 2024

 

Method 1 – Using the SUM Function to Sum Selected Cells in Excel

The following dataset shows sales for different months for a company. We’ll calculate the total sales for C8:C12.

Using SUM Function to Sum Selected Cells

Steps:

  • Use the following formula in the cell C13:

=SUM(C8:C12)

  • Press Enter.

Using SUM Function to Sum Selected Cells

  • You will get the total sales for selected cells.

Using SUM Function to Sum Selected Cells

Read More: How to Sum Range of Cells in Row Using Excel VBA (6 Easy Methods)

Method 2 – Summing Selected Cells with the AGGREGATE Function

We’ll use the same dataset.

Sum Selected Cells Using AGGREGATE Function

Steps:

  • Use the following formula in the cell C13:

=AGGREGATE(9,,C8:C12)

C8:C12 is the range of selected cells for different months, and the sum will be calculated based on these ranges. 9 is the function number, which means the SUM function.

  • Press Enter.

Sum Selected Cells Using AGGREGATE Function

  • You will get the total sales for selected cells.

Sum Selected Cells Using AGGREGATE Function

Similar Readings

Method 3 – Applying the SUBTOTAL Function to Sum Selected Cells

We’ll use the same dataset.

Utilizing SUBTOTAL Function in Excel

Steps:

  • Use the following formula in the cell C13:

=SUBTOTAL(9,C8:C12)

9 is the function number, which brings up the SUM function.

  • Press Enter.

  • Here’s the result.

Utilizing SUBTOTAL Function in Excel

Method 4 – Inserting the SUMIF Function to Sum Specific Excel Cells

We are going to calculate the total sales for the months where the sales value is over $4,500.

Steps:

  • Use the following formula in the cell F5:

=SUMIF(C5:C12,F4)

  • Press Enter.

Applying SUMIF Function in Excel

  • You will get the total sales for selected cells.

Read More: Sum Cells in Excel: Continuous, Random, With Criteria, etc.

Related Articles

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

2 Comments
  1. Reply
    Dave Gilblom Aug 23, 2023 at 10:09 PM

    What I want to be able to do is select a block of numbers, then right-click and get a few choices on what to show about these – sum, average, median, std deviation, for instance, without putting a formula in a cell. The answer should show up in a separate dialogue window when I select an operation.

    Does Excel do this?

    • Reply Avatar photo
      Musiha Mahfuza Mukta Aug 28, 2023 at 4:21 PM

      Thank you Dave Gilblom, for your comment. Yes, Excel can do this. You should use some VBA codes for this. Below, I am attaching these codes.
      –> In the Module 1 write the following code. Which will return the Sum of selected cells.

      Sub CalculateSum()
    Dim selectedRange As Range
    Dim selectedArea As Range
    Dim cell As Range
    Dim sumValue As Double
    On Error Resume Next
    Set selectedRange = Application.Selection
    On Error GoTo 0
    If Not selectedRange Is Nothing Then
        sumValue = 0
        For Each selectedArea In selectedRange.Areas
            For Each cell In selectedArea
                If IsNumeric(cell.Value) Then
                    sumValue = sumValue + cell.Value
                End If
            Next cell
        Next selectedArea
       MsgBox "Sum: " & sumValue
    Else
       MsgBox "No valid range selected."
    End If
End Sub

      –> In the Module 2 write the following code. Which will return the Average of selected cells.

      Sub CalculateSelectedAverage()
    Dim selectedRange As Range
    Dim avgValue As Double
    ' Check if any cells are selected
    If Selection.Cells.count > 0 Then
        ' Set the selected range
        Set selectedRange = Selection
        ' Calculate the average of the selected range
        avgValue = WorksheetFunction.Average(selectedRange)
        ' Display the result in a message box
        MsgBox "The average value of selected cells is: " & avgValue
    Else
        MsgBox "No cells are currently selected."
    End If
End Sub

      –> In the Module 3 write the following code. Which will return the Median of selected cells.

      Sub CalculateSelectedMedian()
    Dim selectedRange As Range
    Dim cell As Range
    Dim valuesArray() As Double
    Dim medianValue As Double
    Dim i As Long, j As Long
    Dim temp As Double
    ' Check if any cells are selected
    If Selection.Cells.count > 0 Then
        ' Set the selected range
        Set selectedRange = Selection
        ' Copy selected cell values to an array
        ReDim valuesArray(1 To selectedRange.Cells.count)
        i = 1
        For Each cell In selectedRange
            valuesArray(i) = cell.Value
            i = i + 1
        Next cell
        ' Sort the values array using bubble sort (simple and not efficient)
        For i = LBound(valuesArray) To UBound(valuesArray) - 1
            For j = i + 1 To UBound(valuesArray)
                If valuesArray(j) < valuesArray(i) Then
                    temp = valuesArray(i)
                    valuesArray(i) = valuesArray(j)
                    valuesArray(j) = temp
                End If
            Next j
        Next i
        ' Calculate the median based on sorted values array
        If UBound(valuesArray) Mod 2 = 0 Then
            medianValue = (valuesArray(UBound(valuesArray) \ 2) + valuesArray(UBound(valuesArray) \ 2 + 1)) / 2
        Else
            medianValue = valuesArray(UBound(valuesArray) \ 2 + 1)
        End If
        ' Display the result in a message box
        MsgBox "The median value of selected cells is: " & medianValue
    Else
        MsgBox "No cells are currently selected."
    End If
End Sub

      –> In the Module 4 write the following code. Which will return the standard deviation of selected cells.

      Sub CalculateSelectedStdDeviation()
    Dim selectedRange As Range
    Dim cell As Range
    Dim valuesArray() As Double
    Dim sum As Double
    Dim mean As Double
    Dim varianceSum As Double
    Dim stdDeviation As Double
    Dim count As Long
    ' Check if any cells are selected
    If Selection.Cells.count > 0 Then
        ' Set the selected range
        Set selectedRange = Selection
        ' Copy selected cell values to an array
        ReDim valuesArray(1 To selectedRange.Cells.count)
        count = 0
        For Each cell In selectedRange
            valuesArray(count + 1) = cell.Value
            sum = sum + cell.Value
            count = count + 1
        Next cell
        ' Calculate the mean
        mean = sum / count
        ' Calculate the sum of squared differences for variance
        For i = 1 To count
            varianceSum = varianceSum + (valuesArray(i) - mean) ^ 2
        Next i
        ' Calculate the variance and standard deviation
        If count > 1 Then
            variance = varianceSum / (count - 1)
            stdDeviation = Sqr(variance)
        Else
            variance = 0
            stdDeviation = 0
        End If
        ' Display the result in a message box
        MsgBox "The standard deviation of selected cells is: " & stdDeviation
    Else
        MsgBox "No cells are currently selected."
    End If
End Sub

      –> Finally, in the ThisWorkbook >> write the following code.

      Private Sub Workbook_Open()
Dim cBar As CommandBar
 Set cBar = Application.CommandBars("Cell")
 With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSum"
 .Caption = "Sum"
  .FaceId = 213
 .Tag = "My_Cell_Control_Tag"
 End With
  With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedAverage"
 .Caption = "Average"
  .FaceId = 17
 .Tag = "My_Cell_Control_Tag"
 End With
  With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedMedian"
 .Caption = "Median"
  .FaceId = 50
 .Tag = "My_Cell_Control_Tag"
 End With
 With cBar.Controls.Add(Type:=msoControlButton, Before:=1)
 .OnAction = "'" & ThisWorkbook.Name & "'!CalculateSelectedStdDeviation"
 .Caption = "StdDeviation"
  .FaceId = 2130
 .Tag = "My_Cell_Control_Tag"
 End With
End Sub

      –> Now, save the code >> press on Run button >> close the Excel file >> re open the file >> select some cells >> right click >> from the Context Menu Bar.

      –> Then, choose the desire operation >> get the answer in MsgBox.

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

ExcelDemy Consulting Services

Submit Excel Task

 

See Our Review attrustpilot review

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo