Log in
x
or
x
x
Register
x

or
0
0
0
s2sdefault

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.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Kcc C · 20 days ago
    this doesn't work if you change the colors in the cells: so if you change yellow to red or yellow to blank, it doesn't change the count. if you add/create new colored cells then yes it updates the count, but if you change the color of a cell that has already been counted, then the count will not update.
  • To post as a guest, your comment is unpublished.
    Casey · 25 days ago
    This has been so useful! Thanks very much for your help
  • To post as a guest, your comment is unpublished.
    Carolyn · 1 months ago
    The =colorfunction WAS working great for me ... got everything as I wanted it to work and then copied the sheet repeatedly ... then around the 10th copied sheet the formula stopped working ... what did I do wrong? How do I fix it?
  • To post as a guest, your comment is unpublished.
    luisa · 1 months ago
    hey, we used the colorfunction and it works fine, however as soon as we close the sheet and open it again the next day, we have to copy+paste+save it all over again, is there any way to save so it will always be there? we save the sheet in a folder that's shared within many computers. Thanks!
    • To post as a guest, your comment is unpublished.
      Tim · 1 months ago
      Luisa,
      When you open VBA, you have VBAProject (sheet1) its the place for makros for THIS SHEET. But you also have VBA Projects (PERSONAL.XLSB). Add module in PERSONAL and paste this makro there. This way you will have function avialable in every file on your computer.
      Im not sure but probably you dont have this PRESONAL.XLSB automaticly, you need to create it.
      Probably not the the fastest way but : click record macro, window will apear where you put name (whatever), shortcut (whatever) and place to store makro (chose personal macros sheet). Click ok, record any makro (puting 1 in cell A1), stop recording. Now you should have PERSONAL.XLSB in the list.
      Hope it works and helps :)
  • To post as a guest, your comment is unpublished.
    Justin · 1 months ago
    This works great unless you are trying to count colors from conditional formatting. Has anybody figured out how to solve that one?