How can I save this code and apply the following formula?

## How to count and sum cells based on background color in Excel?

Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color. In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.

**Count and Sum cells based on specific fill color by Filter and SUBTOTAL**

**Count and Sum cells based on specific fill color by GET.CELL function**

**Count and sum cells based on specific fill color with User Defined Function**

**Count and Sum cells based on specific fill color with Kutools Functions**

**Count and Sum cells based on specific fill color (or conditional formatting color) with Kutools for Excel**

### One click to count, sum, and average colored cells in Excel

With the excellent ** Count by Color** feature of **Kutools for Excel**, you can quickly count, sum, and average cells by specified fill color or font color with only one click in Excel. Besides, this feature will also find out the max and min values of cells by the fill color or font color.
**Full Feature Free Trial 60-day!**

#### **Count and Sum colored cells by Filter and SUBTOTAL**

Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column. In this situation, we can filtered the Amount column by color, and then count or sum filtered colored cells by the SUBTOTAL function easily in Excel.

**1**. Select blank cells to enter the SUBTOTAL function.

- To count all cells with the same background color, please enter the formula
**=SUBTOTAL(102, E2:E20)**; - To sum all cells with the same background color, please enter the formula
**=SUBTOTAL(109, E2:E20)**;

**Note**: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need.

**2**. Select the header of the table, and click **Data **> **Filter**. See screenshot:

**3**. Click the Filter icon in the header cell of the Amount column, and click** Filter by Color **and the specified color you will count by successively. See screenshot:

After filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. See screenshot:

**Note**: This method requires the colored cells you will count or sum are in the same column.

#### **Count or Sum colored cells by GET.CELL function**

In this method, we will create a named range with the GET.CELL function, get the color code of cells, and then count or sum by the color code easily in Excel. Please do as follows:

**1**. Click **Formulas** > **Define Name**. See screenshot:

**2**. In the New Name dialog, please do as below screenshot shown:

(1) Type a name in the Name box;

(2) Enter the formula **=GET.CELL(38,Sheet4!$E2) **in the Refers to box (**note**: in the formula, *38* means return the cell code, and *Sheet4!$E2* is the first cell in the Amount column except the column header which you need to change based on your table data.)

(3) Click the **OK **button.

**3**. Now add a new Color column right to the original table. Next type the formula **=NumColor** , and the drag the AutoFill handle to apply the formula to other cells in the Color column. See screenshot:

**Note**: In the formula, *NumColor* is the named range we specified in the first 2 steps. You need to change it to the specified name you set.

Now the color code of each cell in the Amount column returns in the Color Column. See screenshot:

**4**. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown:

A. To count cells by color, please enter the formula **=COUNTIF($F$2:$F$20,NumColor)**;

B. To sum cells by color, please enter the formula **=SUMIF($F$2:$F$20,NumColor,$E$2:$E$20)**.

**Note**: In both formulas, *$F$2:$F$20 *is the Color column, *NumColor* is the specified named range, *$E$2:$E$20*** **is the Amount Column, and you can change them as you need.

Now you will see the cells in the Amount column are counted and sum by their fill colors.

#### ** Count and sum cells based on specific fill color with User Defined Function**

Supposing the colored cells scatter in a range as below screenshot shown, both above methods cannot count or sum the colored cells. Here, this method will introduce a VBA to solve the problem.

**1**. Hold down the **ALT + F11** keys, and it opens the **Microsoft Visual Basic for Applications** window.

**2**. Click **Insert** >** Module**, and paste the following code in the Module Window.

**VBA: Count and sum cells based on background color:**

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function

**3**. Then save the code, and apply the following formula:

A. Count the colored cells:** =colorfunction(A,B:C,FALSE)**

B. Sum the colored cells:** =colorfunction(A,B:C,TRUE)**

Note: In above formulas, **A** is the cell with the particular background color you want to calculate the count and sum, and **B:C** is the cell range where you want to calculate the count and sum.

**4**. Take the following screenshot for example, enter the formula**=colorfunction(A1,A1:D11,FALSE)** to count the yellow cells. And use the formula **=colorfunction(A1,A1:D11,TRUE)** to sum the yellow cells. See screenshot:

**5**. If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:

#### ** Count and Sum cells based on specific fill color with Kutools Functions**

Kutools for Excel also supports some useful functions to help Excel users to make special calculations, says count by cell background color, sum by font color, etc.

**Kutools for Excel **- Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required!** Get it now!**

**1**. Select the blank cell you place the counting results, and click **Kutools** > **Kutools Functions** >** Statistical & Math** > **COUNTBYCELLCOLOR**. See screenshot:

**2**. In the Function Arguments dialog, please specify the range you will count colored cells within in the** Reference** box, choose the cell that is filled by the specified background color in the **Color_index_nr** box, and click the **OK** button. See screenshot:

**Notes:**

(1) You can also type the specified Kutools Function **=COUNTBYCELLCOLOR($A$1:$E$20,G2)** in the blank cell or formula bar directly to get the counting results;

(2) Click **Kutools** > **Kutools Functions** > **Statistical & Math** > **SUMBYCELLCOLOR** or type **=SUMBYCELLCOLOR($A$1:$E$20,G2)** in the blank cell directly to sum cells based on the specified background color.

Apply the **COUNTBYCELLCOLOR **and **SUMBYCELLCOLOR** functions for each background color separately, and you will get the results as below screenshot shown:

**Kutools Functions** contain a number of built-in functions to help Excel users calculate easily, including Count / Sum / Average Visible cells, Count / Sum by cell color, Count / Sum by font color, Count characters, Count by font bold, etc. **Have a Free Trial!**

#### ** Count and Sum cells based on specific fill color with Kutools for Excel**

With the above User Defined Function, you need to enter the formula one by one, if there are lots of different colors, this method will be tedious and time-consuming. But if you have **Kutools for Excel**’s **Count by Color** utility, you can quickly generate a report of the colored cells. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.

**Kutools for Excel **- Includes more than 300 handy Excel tools. Full feature free trial 60-day, no credit card required!** Get it now!**

**1**. Select the range that you want to use, and click **Kutools Plus** > **Count by Color**, see screenshot:

**2**. And in the **Count by Color** dialog box, please do as below screenshot shown:

(1) Select **Standard formatting** from the **Color method** drop down list;

(2) Select **Background **from the **Count type** drop down list.

(3) Click the Generate report button.

**Note**: To count and sum colored cells by specific conditional formatting color, please select** Conditional formatting** from the **Color method** drop down list in above dialog, or select** Standard and Conditional formatting **from the drop down list to count all cells filled by the specified color.

Now you will get a new workbook with the statistics. See screenshot:

The **Count by Color** feature calculates (Count, Sum, Average, Max, etc.) cells by background color or font color. **Have a Free Trial!**

#### Related article:

How to count / sum cells based on the font colors in Excel?

#### ** Demo: Count and sum cells based on background, conditional formatting color: **

In this Video, the **Kutools** tab and the **Kutools Plus** tab are added by **Kutools for Excel**. If need it, please click **here** to have a 60-day free trial without limitation!

** Recommended Productivity Tools**

**Office Tab**

** Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.**

**Kutools for Excel**

**Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!**

**300 New Features for Excel, Make Excel Much Easy and Powerful:**

- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

###### You are guest ( Sign Up? )

###### or post as a guest, but your post won't be published automatically.

- ← Previous
- ...
- ...
- Next →