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

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 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.
    WAQAS · 3 years ago
    Remarkable
    Very helpful
    continue your good work
  • To post as a guest, your comment is unpublished.
    Anon · 3 years ago
    I had issues with this discovering different shades of colours. It looks like different shades can have the same colour index which then gives incorrect counts.

    I'm using Interior.Color instead of Interior.ColorIndex which gives the correct result.
    • To post as a guest, your comment is unpublished.
      Megan · 2 years ago
      Thank you, this saved the day for me!
  • To post as a guest, your comment is unpublished.
    Waqas · 3 years ago
    Really helpful. Thank you.
  • To post as a guest, your comment is unpublished.
    Ashish · 3 years ago
    It's perfect. But I was expecting some improvement. Instead of passing cell number to pick up the base color, can I pass the color itself in the formula?

    [quote]Example: instead of [b]=colorfunction(G31,C3:G27,FALSE)[/b], can I do [u][b]=colorfunction(FF00FF,C3:G27,FALSE)[/b][/u][/quote]

    Otherwise, it's amazing. Thanks!!
  • To post as a guest, your comment is unpublished.
    FRomero · 3 years ago
    So... I want to count all cells colored pink (Q3) and with a criteria of >50...

    Here is the formula...
    =COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

    It keeps returning "0" which is wrong, what can to get the real Value?
    • To post as a guest, your comment is unpublished.
      Ty · 3 years ago
      [quote name="FRomero"]So... I want to count all cells colored pink (Q3) and with a criteria of >50...

      Here is the formula...
      =COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

      It keeps returning "0" which is wrong, what can to get the real Value?[/quote]

      Hey FRomero,

      Well first thing that I see is that you are combining the colorfunction within a CountIF function. Have you established that you are able to attain the desired result by using helper cells. Thus, one cell to determine the value of the Colorfunction and then a second cell to determine the remainder of the CountIF?
      I personally have only used a range including up to 1,000 cells. Is the Colorfunction capabale of a range so large? Just some questions that might help you acheive the answer.
      later

      Ty
      • To post as a guest, your comment is unpublished.
        Dan · 2 years ago
        [quote name="Ty"][quote name="FRomero"]So... I want to count all cells colored pink (Q3) and with a criteria of >50...

        Here is the formula...
        =COUNTIFS(CA!A2:A10000,colorfunction(Q3,CA!B2:B10000,FALSE),CA!B2:B10000,">50")

        It keeps returning "0" which is wrong, what can to get the real Value?[/quote]

        Hey FRomero,

        Well first thing that I see is that you are combining the colorfunction within a CountIF function. Have you established that you are able to attain the desired result by using helper cells. Thus, one cell to determine the value of the Colorfunction and then a second cell to determine the remainder of the CountIF?
        I personally have only used a range including up to 1,000 cells. Is the Colorfunction capabale of a range so large? Just some questions that might help you acheive the answer.
        later

        Ty[/quote]

        Did you ever get an answer that makes this work? I need to do the same thing. I've been trying to modify the colorfunction code itself, but i'm not programmer so I can't get it to work.