﻿

## 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 colored cells with User Defined Function

Count/Sum and do other calculations of colored cells with Kutools for Excel

#### Recommended Productivity Software

Office Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).

### Hint

The following code can help you count and sum the cells with a certain background color, please do as this:

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 backgroud 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:

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

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

A: is the cell with the particular background color you want to calculate the count and sum.

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:D7,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D7,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/Sum and do other calculations of colored cells 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 120 handy Excel tools. Free to try with no limitation in 30 days. Read More   Free Download Now

If you have installed Kutools for Excel, please do as following steps:

1. Select the range that you want to use.

2. Click Enterprise > Count by Color, see screenshot:

3. And in the Count by Color dialog box, all of the colors have been counted. You can filter the color cells based on your needs. For example, you want to count the cells whose background is generated by standard formatting (or conditional formatting), you can filter as below:

(1) Click the Color method box and then select Standard formatting (or Conditional formatting) from drop down list;

(2) Click the Count type box and select Background from drop down list.

4. And then click Generate report button, you will get a new workbook with the statistics. See screenshot:

### Recommended Productivity Tools

The following tools will greatly save your time and effort, which one do you prefer?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.

### Kutools for Excel

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

More than 120 powerful advanced functions which designed for Excel:

• 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,

Refresh