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.

**Table of Contents**hide

## 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.

__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**.

** Step-01**:

âž¤Write the color of cells of the

**Sales column**manually in the

**Color column**.

** 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.

âž¤Press **ENTER**

** Result**:

Now, you will get the

*Total Sales of Apple*which is

**$8,863.00**

__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**.

** Step-01**:

âž¤Select the data table

âž¤Go to

**Insert**Tab>>

**Table**Option

Then the **Create Table **Dialog Box will appear.

âž¤Click the **My table has headers **option.

âž¤Press **OK**.

After that, the table will be created.

** Step-02**:

âž¤Click the Dropdown icon in the

**Sales column**

âž¤Select the **Filter by Color **Option

âž¤Choose the Green colored box as **Filter by Cell Color**âž¤Press

**OK**

Now, the table will be filtered by Green color.

** 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.

âž¤Press **ENTER**

** Result**:

Afterward, you will get the

*Total Sales of Apple*which is

**$8,863.00**

__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**.

** 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.

âž¤Press **ENTER**

Then, you will get the *Total Sales*

** Step-02**:

âž¤Select the data range

âž¤Go to

**Data**Tab>>

**Sort & Filter**Dropdown>>

**Filter**Option

âž¤Click the Dropdown icon in the **Sales column**

âž¤Select theÂ **Filter by Color **Option

âž¤Choose the Green colored box as **Filter by Cell Color**âž¤Press

**OK**

** Result**:

Afterward, you will get the

*Total Sales of Apple*which is

**$8,863.00**

__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**

** 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.

âž¤Press **ENTER**

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

** Step-02**:

âž¤Follow

**Step-02**of

**Method-3**

Finally, you will get the sum of the number of Green colored cells

__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Â **

** Step-01**:

âž¤Select the data table

âž¤Go to

**Home**Tab>>

**Editing**Dropdown>>

**Find & Select**Dropdown>>

**Find**Option

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

âž¤Select the **Format **Option

Then, **Find Format **Dialog Box will appear

âž¤Select **Fill **Option and choose the Green Color

âž¤Press **OK**

âž¤Click **Find All**

** 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.

__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.

** Step-01**:

âž¤Go to

**Formulas**Tab>>

**Defined Names**Dropdown>>

**Name Manager**Option

Then **Name Manager **Wizard will appear

âž¤Select the **New **Option

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**

** Step-02**:

âž¤Create a column named

**Code**

âž¤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

âž¤Press** ENTER**âž¤Drag Down the

**Fill Handle**Tool.

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

** 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.

** Result**:

Now, you will get the

*Total Sales of Apple*which is

**$8,863.00**

ðŸ““**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.

** Step-01**:

âž¤Follow

**Step-01**and

**Step-02**of

**Method-6**

** 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

** Result**:

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

## 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.

## 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.