How to Sum Colored Cells in Excel Without VBA (7 Ways)

If you want to know some of the easiest ways to sum colored cells in Excel without VBA then you will find this article useful. While working with Excel sometimes it becomes necessary to sum the values of the colored cells or sum the number of colored cells quickly. So, let’s dive into the article to get to know the ways to do this job.

Download Workbook

7 Ways to Sum Colored Cells in Excel Without VBA

Here, I have a dataset where for Apples the Sales are colored as Green. By using the following methods you will be able to sum the Sales value based on this color or sum the number of Green cells in this table. For this purpose, I am using Microsoft Excel 365 version, you can use any of the versions according to your convenience.

Dataset

Method-1: Using SUMIF function to SUM the Values of the Colored Cells

For getting the Total Sales of Apple based on its color you can use the SUMIF function. To do this task, I have added a column named Color.

using SUMIF function

Step-01:
➤Write the color of cells of the Sales column manually in the Color column.

using SUMIF function

Step-02:
➤Select the output Cell D12
➤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.

using SUMIF function

➤Press ENTER

Result:
Now, you will get the Total Sales of Apple which is $8,863.00

using SUMIF function

Method-2: Creating Table to SUM the Values of the Colored Cells

If you want to know the Total Sales of Apple based on its color you can use the Table Option and the SUBTOTAL function.

creating table

Step-01:
➤Select the data table
➤Go to Insert Tab>>Table Option

creating table

Then the Create Table Dialog Box will appear.
➤Click the My table has headers option.
➤Press OK.

creating table

After that, the table will be created.

creating table

Step-02:
➤Click the Dropdown icon in the Sales column

creating table

➤Select the Filter by Color Option
➤Choose the Green colored box as Filter by Cell Color
➤Press OK

creating table

Now, the table will be filtered by Green color.

creating table

Step-03:
➤Select the output Cell D12
➤Type the following formula

=SUBTOTAL(109,D5:D9)

109 is for the SUM function, D5:D9 is the range of cells.

creating table

➤Press ENTER

Result:
Afterward, you will get the Total Sales of Apple which is $8,863.00

sum colored cells in Excel without VBA

Method-3: Using Filter Option to SUM the Values of the Colored Cells

You can have the Total Sales of Apple based on its color by using the Filter Option and the SUBTOTAL function.

using Filter

Step-01:
➤Select the output Cell D12
➤Type the following formula

=SUBTOTAL(109,D5:D11)

109 is for the SUM function, D5:D11 is the range of cells.

using Filter

➤Press ENTER

Then, you will get the Total Sales

using Filter

Step-02:
➤Select the data range
➤Go to Data Tab>>Sort & Filter Dropdown>> Filter Option

using Filter

➤Click the Dropdown icon in the Sales column

using Filter

➤Select the  Filter by Color Option
➤Choose the Green colored box as Filter by Cell Color
➤Press OK

using Filter

Result:
Afterward, you will get the Total Sales of Apple which is $8,863.00

using Filter

Method-4: Using Filter Option to SUM the Number of the Colored Cells

If you want to know the sum of the number of Green colored cells or count the Green colored cells then you can use the Filter Option and the SUBTOTAL function

using Filter

Step-01:
➤Select the output Cell C12
➤Type the following formula

=SUBTOTAL(103,B5:B11)

103 is for the COUNTA function, B5:B11 is the range of cells.

using Filter

➤Press ENTER

Now, you will get the sum of the total number of cells.

using Filter

Step-02:
➤Follow Step-02 of Method-3
Finally, you will get the sum of the number of Green colored cells

sum colored cells in Excel without VBA

Method-5: Using Find & Select Option to SUM the Number of the Colored Cells

For having the sum of the number of Green colored cells or count the Green colored cells then you can use the Find & Select Option 

Find & Select

Step-01:
➤Select the data table
➤Go to Home Tab>>Editing Dropdown>>Find & Select Dropdown>>Find Option

Find & Select

After that, the Find and Replace Dialog Box will pop up.

➤Select the Format Option

Find & Select

Then, Find Format Dialog Box will appear

➤Select Fill Option and choose the Green Color
➤Press OK

Find & Select

➤Click Find All

Find & Select

Result:
Then, you can see the total number of Green colored cells in the bottom corner of the dialog box which indicates that there is a total of 3 colored cells.

Find & Select

Method-6: Using GET.CELL function to SUM the Values of the Colored Cells

You can use the GET.CELL function to sum up the Sales for Green colored cells.

using GET.CELL

Step-01:
➤Go to Formulas Tab>>Defined Names Dropdown>>Name Manager Option

using GET.CELL

Then Name Manager Wizard will appear

➤Select the New Option

using GET.CELL

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!$D2)

38 will return the Color Code and SUM!$D2 is the colored cell in the SUM sheet.
➤Finally, Press OK

using GET.CELL

Step-02:
➤Create a column named Code

using GET.CELL

➤Type the following formula in the output cell E5

=ClrCode

It is the function we have created in the previous step and it will return the Code of the Colors

using GET.CELL

➤Press ENTER
➤Drag Down the Fill Handle Tool.

using GET.CELL

In this way, you will get the color codes for all of the cells

using GET.CELL

Step-03:
➤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 GET.CELL

Result:
Now, you will get the Total Sales of Apple which is $8,863.00

using GET.CELL

📓Note:
You have to save the Excel file as a Macro-enabled Workbook because of using the GET.CELL function.

Method-7: Using GET.CELL to SUM the Number of the Colored Cells

You can use the GET.CELL function to sum the number of Green colored cells.

using GET.CELL

Step-01:
➤Follow Step-01 and Step-02 of Method-6

using GET.CELL

Step-02:
➤Select the output Cell G5
➤Type the following formula

=COUNTIF(E5:E11,ClrCode)

E5:E11 is the criteria range, ClrCode is the criteria

using GET.CELL

Result:
After that, you will get the total number of Green colored cells in the range.

using GET.CELL

Practice Workbook

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice

Conclusion

In this article, I tried to cover the easiest ways to sum colored cells in Excel without VBA effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo