Tip: Other languages are Google-Translated. You can visit the English version of this link.
or

Register

or
0
0
0
s2smodern

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

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

###### Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

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

 : with more than 120 handy Excel add-ins, free to try with no limitation in 60 days.

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:

(1.) Click the Color method box and then select Standard 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:

#### Count and Sum cells based on specific conditional formatting color with Kutools for Excel

If you have the cells with conditional formatting colors, the Count by Color utility also can do you a favor.please do as follows:

1. Select the cells range you want to count or sum the cells by conditional formatting color, then click Enterprise > Count by Color.

2. In the Count by Color dialog box:

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

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

3. And then click Generate report button, the calculated result will be reported in a new workbook, see screenshot:

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:

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

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

Say something here...
symbols left.
###### or post as a guest, but your post won't be published automatically.
People in conversation:
• To post as a guest, your comment is unpublished.
· 4 days ago
works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
• To post as a guest, your comment is unpublished.
· 1 months ago
This doesn't work at all. It's total BS.
• To post as a guest, your comment is unpublished.
· 3 months ago
I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.
• To post as a guest, your comment is unpublished.
· 4 months ago
i want to colour in excel by using if function.
• To post as a guest, your comment is unpublished.
· 5 months ago
Fix your formula, its not "=colorfunction(A,B:C,FALSE)", but "=colorfunction(A;B:C;FALSE)" its a semi-colon, not a comma.
• To post as a guest, your comment is unpublished.
· 4 months ago
I get an error when using ; but using, calculates fine?