How to sum highlighted cells in Microsoft Excel

“Sum highlighted cells” usually refers to the process of adding up the values of cells that have been visually selected or highlighted in a spreadsheet or table. This is a common operation in spreadsheet applications such as Microsoft Excel.

In this tutorial, we will learn how to sum highlighted cells in Microsoft Excel. Although Microsoft Excel doesn’t have a specific built-in function to sum highlighted cells, there are alternative methods you can use to achieve the same result i.e. you can use the Filter feature or write a VBA code to sum the values of the highlighted cells. These options allow you to perform the summation without having to manually select each cell.

Method 1: Using the Filter Feature

Step 1 – Click on the Sort & Filter Button

• Click on the Sort & Filter Button in the Editing section of the Home tab.

Step 2 – Click on the Filter Option

• Click on the Filter option.
• The Filter Arrows will appear next to each column header.

Step 3 – Click on the Filter Arrow and Select the Filter by Colour option

• Click on the Filter Arrow.
• A drop-down menu will appear.
• Click on the Filter by colour option.

Step 4 – Select the Colour 

• Select the colour that is used to highlight the cells.
• Only the rows containing the highlighted cells will be displayed.

Step 5 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell in which you want to sum up the highlighted cell.

Step 6 – Use the SUBTOTAL Function

• We will use the SUBTOTAL function to sum the highlighted cell because the SUBTOTAL function only sums up the visible cells where as the SUM function does not.
• The syntax of the SUBTOTAL function will be: 

                             SUBTOTAL( 9 , C2:C10 ) 

Where 9 represents the function to be performed.

The second argument C2:C10 is the range containing the highlighted cells, to sum up.

Step 7 – Press the Enter key

• Press the Enter key to get the sum of highlighted cells.

Method 2: Using VBA Code

Step 1 – Locate the Develepor Tab 

• Locate the Develepor tab.

Step 2 – Click on the Visual Basic Button 

• Click on the Visual Basic button in the Develepor tab.

Step 3 – Insert a New Module

• Right-click on the sheet name in the Project-VBA Project menu at the left of the window.
• Click on Insert.
• Click on Module

Step 4 – Paste this Code and Close the VB Editor

• Copy and  paste this code:

Function SumByColor(SumRange As Range, SumColor As Range)

Dim SumColorValue As Integer

Dim TotalSum As Long

SumColorValue = SumColor.Interior.ColorIndex

Set rCell = SumRange

For Each rCell In SumRange

If rCell.Interior.ColorIndex = SumColorValue Then

TotalSum = TotalSum + rCell.Value

End If

Next rCell

SumByColor = TotalSum

End Function

• Close the VB editor.

Step 5 – Select a Blank Cell and Place an Equals Sign

• Select a blank cell and place an equals sign.

Step 6 – Enter the Created Function “SumByColor” 

• Enter the “SumByColor” function right next to the equals sign.
• Its syntax will be : 

                         SumByColor( C2:C10 , C2)

• Where the first argument is the range containing the highlighted cells.
• The second argument i.e. C2 is the cell containing the highlight colour of the cells to be sum up.

Step 7 – Press the Enter Key

• Press the Enter key.
• Sum of the highlighted cells will be displayed.