Log in  \/ 
x
or
 Use Facebook account  Use Google account  Use Microsoft account  Use LinkedIn account
x
x
Register  \/ 
x

or
 Use Facebook account  Use Google account  Use Microsoft account  Use LinkedIn account

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 Kutools for Excel

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


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


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:

doc count sum by color 1

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

doc count sum by color 2


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

doc count sum by color 3

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.

doc count sum by color 4

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

doc count sum by color 5

Click to Download and free trial Kutools for Excel Now !


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.

doc count sum by color 6

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

doc count sum by color 7

If you want to know more about this feature, please click Count by Color

Click to Download and free trial Kutools for Excel Now !


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. Download and free trial Now!


Recommended Productivity Tools

Office Tab

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

Kutools for Excel

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

gold star1 200 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...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

 

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Hellen Hornby · 4 days ago
    Hi I tried the above in Excel and keep getting a 'ambiguous name detected' error message related to the colorfunction. Any thoughts?
  • To post as a guest, your comment is unpublished.
    a · 6 days ago
    the function does not differentiate between colours. it just counts any cell this is coloured.
  • To post as a guest, your comment is unpublished.
    Andrea · 2 months ago
    Hi

    Fantastic tool and really easy to use so thank you!

    Is there any update on using this with conditional formatting? I think there is a fix above but I didn't understand how it worked. Simple instructions for a novice would be much appreciated!

    Thanks
  • To post as a guest, your comment is unpublished.
    saurabh · 2 months ago
    everything is good but what if i want to add cells across multiple worksheet
  • To post as a guest, your comment is unpublished.
    Hien · 2 months ago
    $2,322.29 2322.29
    $100.00 128.66
    $76.13 76.13
    $450.02 450.02
    $28.66 128.66
    $544.31 488.77

    0 0
    $278.66
    $636.76 636.76
    Please help the numbers on the left are the correct number but when i dragged down the formula i get the numbers on the right. As you can see some of the numbers are correct but others come out wrong. Please help me, i dont understand why this is not working. Thanks.