How to Sum Colored Cells in Excel Without VBA: 5 Ways

Method 1 – Using SUMIF Function

Steps:

➤ Write the color of cells of the Sales column manually in the Color column.

writing color name in newly created column

➤ Select the output cell E12.
➤ Type the following formula.

=SUMIF(E5:E11,"Green",D5:D11)

E5:E11 is the criteria_range, Green is the criteria and D5:D11 is the sum_range.
➤ Press ENTER.

Using SUMIF Function to sum colored cells in excel without vba

Result:
NGet the Total Sales of Apple which is $8,863.

Method 2 – Creating Table to Sum Values of Colored Cells

Steps:

➤ Select the data table (B4:D11).
➤ Go to Insert Tab>>Table Option
The Create Table dialog box will appear.
➤ Click the My table has headers option.
➤ Press OK.

Creating Table to Sum Values of Colored Cells

The table will be created.

Table created

➤Click the Dropdown icon in the Sales column.
➤ Select the Filter by Color option.
➤ Choose the green box as Filter by Cell Color.
➤ Press OK.

filter by color

The table will be filtered by green.

only green cells are visible

➤ Select the output cell D12.
➤ Type the following formula.

=SUBTOTAL(109,D5:D9)

109 is for the SUM function, and D5:D9 is the range of cells containing Sales.
➤ Press ENTER.

SUBTOTAL function to sum colored cells in Excel without vba

Result:
The Total Sales of Apple is $8,863.

Method 3 – Utilizing Filter Option to Sum Colored Cells

Steps:

➤ Select the output cell D12.
➤ Type the following formula

=SUBTOTAL(109,D5:D11)

109 is for the SUM function, and D5:D11 is the range of cells.
➤ Press ENTER.

SUBTOTAL function to sum colored cells in excel

Then, you will get the total Sales.

➤ Select the data range.
➤ Go to Data tab>>Sort & Filter dropdown>> Filter option.

Applying Filter

You can see filter buttons beside each column heading.

Filter Button in Excel

➤ Click the Dropdown icon in the Sales column.
➤ Select the Filter by Color option.
➤ Choose the green box as Filter by Cell Color.
➤ Press OK.

Filter by color to sum colored cells in excel without vba

Result:
The Total Sales of Apple is $8,863.

Utilizing Filter Option to Sum Colored Cells in Excel

3.2 Count of Colored Cells

Steps:

➤ Select the output cell D12.
➤ Type the following formula.

=SUBTOTAL(103,D5:D11)

103 is for the COUNTA function, and D5:D11 is the range of cells.
➤ Press ENTER.
You will get the sum of the total number of cells.
➤ Follow the previous steps of Method-3.1.
You will get the sum of the number of Green colored cells.

Utilizing Filter Option to Sum Count of Colored Cells in Excel without VBA

Method 4 – Incorporating Find & Select Option

Steps:

➤ Select the data table (B4:D11).
➤ Go to Home tab>>Editing dropdown>>Find & Select dropdown>>Find option.

Incorporating Find & Select Option

After that, the Find and Replace dialog box will pop up.
➤ Select the Format option.

Find and Replace dialog box in Excel

The Find Format dialog box will appear
➤ Select the Fill tab and choose the Green color.
➤ Press OK.

using Find Format dialog box to sum count of colored cells in excel

➤ Click Find All.

Find and Replace dialog box in Excel to sum colored cells

Result:
The total number of Green colored cells is in the bottom corner of the dialog box, which indicates that there is a total of 3 green colored cells.

Count 3 colored cells in Excel worksheet

Method 5 – Applying GET.CELL Method

5.1 Sum Values of Colored Cells

The GET.CELL function to sum up the Sales for Green colored cells.

Steps:

➤ Go to Formulas tab>>Defined Names dropdown>>Name Manager option.

Name Manager option

The Name Manager Wizard will appear.
➤ Select the New option.

opening Name Manager dialog box

After that, the New Name dialog box will pop up.
➤ Type any type of name in the Name box, here I have used ClrCode.
➤ Select the Workbook option in the Scope box.
➤ Type the following formula in the Refers to box.

=GET.CELL(38,SUM!$D5)

38 will return the Color Code and SUM!$D5 is the colored cell in the SUMIF sheet.
➤ Press OK.

Applying GET.CELL Method to sum colored cells in excel

➤ Create a column named Code.

creating new column for color code in excel

➤ Type the following formula in the output cell E5.

=ClrCode

➤ Press ENTER.

ClrCode function

It will return the Code of the colors.
➤ Double-click on the Fill Handle Tool.

using Fill Handle to sum colored cells in Excel

You will get the color codes for all of the cells.

color codes for cells in excel

➤ Select the output cell G5.
➤ Type the following formula.

=SUMIF(E5:E11,ClrCode,D5:D11)

E5:E11 is the criteria_range, ClrCode is the criteria and D5:D11 is the sum_range.

using SUMIF function to sum colored cells in excel without vba

Result:
The Total Sales of Apple is $8,863.

Note: Save the Excel file as a Macro-enabled Workbook because of the GET.CELL function.

5.2 Count of Colored Cells

Steps:

➤ Follow the previous steps of Method-5.1.
➤ Select the output cell G5.
➤ Type the following formula.

=COUNTIF(E5:E11,ClrCode)

E5:E11 is the criteria_range, and ClrCode is the criteria.

Applying GET.CELL Method to sum count of colored cells in Excel

Result:
You will get the total number of Green colored cells in the range.

How to Sum Colored Cells in Excel with VBA

Steps:

➤ Go to the Developer tab >> Visual Basic option.

opening Visual Basic from Developer tab ribbon

The Microsoft Visual Basic for Applications window will open.
➤ Jump to the Insert tab.
➤ Select Module from the options.

Inserting Module in VBE

It opens the code module where you need to paste the code below.

Function Sum_Red_Cells(cc As Range, rr As Range)
Dim x As Long
Dim y As Integer
y = cc.Interior.ColorIndex
For Each i In rr
If i.Interior.ColorIndex = y Then
x = WorksheetFunction.Sum(i, x)
End If
Next i
Sum_Red_Cells = x
End Function

VBA code to sum colored cells in excel

Navigate to the VBA worksheet “VBA”.
➤ Select cell D14 and start to write the function name we just created.
You can see that the function name appears just after writing down =sum in the cell.
➤Select the function Sum_Colored_Cells and press the TAB key on the keyboard.

calling Sum_Colored_Cells function

Give the necessary arguments for the function. D13 is the cell reference for the green-colored cell. D5:D11 is the cell range to perform the sum operation.

Applying VBA to Sum Colored Cells in Excel