Log in  \/ 
x
or
x
x
Register  \/ 
x

or

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 by Filter and SUBTOTAL

Count and Sum cells based on specific fill color by GET.CELL function

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

Count and Sum cells based on specific fill color with Kutools Functions

Count and Sum cells based on specific fill color (or conditional formatting color) with Kutools for Excel

One click to count, sum, and average colored cells in Excel

With the excellent Count by Color feature of Kutools for Excel, you can quickly count, sum, and average cells by specified fill color or font color with only one click in Excel. Besides, this feature will also find out the max and min values of cells by the fill color or font color. Full Feature Free Trial 30-day!
ad count by color 2


Count and Sum colored cells by Filter and SUBTOTAL

Supposing we have a fruit sales table as below screenshot shown, and we will count or sum the colored cells in the Amount column. In this situation, we can filtered the Amount column by color, and then count or sum filtered colored cells by the SUBTOTAL function easily in Excel.

1. Select blank cells to enter the SUBTOTAL function.

  1. To count all cells with the same background color, please enter the formula =SUBTOTAL(102, E2:E20);
  2. To sum all cells with the same background color, please enter the formula =SUBTOTAL(109, E2:E20);


Note: In both formulas, E2:E20 is the Amount column containing the colored cells, and you can change them as you need.

2. Select the header of the table, and click Data > Filter. See screenshot:

3. Click the Filter icon  in the header cell of the Amount column, and click Filter by Color and the specified color you will count by successively. See screenshot:

After filtering, both SUBTOTAL formulas counting and summing all filtered color cells in the Amount column automatically. See screenshot:

Note: This method requires the colored cells you will count or sum are in the same column.


Count or Sum colored cells by GET.CELL function

In this method, we will create a named range with the GET.CELL function, get the color code of cells, and then count or sum by the color code easily in Excel. Please do as follows:

1. Click Formulas > Define Name. See screenshot:

2. In the New Name dialog, please do as below screenshot shown:
(1) Type a name in the Name box;
(2) Enter the formula =GET.CELL(38,Sheet4!$E2) in the Refers to box (note: in the formula, 38 means return the cell code, and Sheet4!$E2 is the first cell in the Amount column except the column header which you need to change based on your table data.)
(3) Click the OK button.

3. Now add a new Color column right to the original table. Next type the formula =NumColor , and the drag the AutoFill handle to apply the formula to other cells in the Color column. See screenshot:
Note: In the formula, NumColor is the named range we specified in the first 2 steps. You need to change it to the specified name you set.

Now the color code of each cell in the Amount column returns in the Color Column. See screenshot:

4. Copy and list the fill color in a blank range in the active worksheet, and type formulas next to it as below screenshot shown:
A. To count cells by color, please enter the formula =COUNTIF($F$2:$F$20,NumColor);
B. To sum cells by color, please enter the formula =SUMIF($F$2:$F$20,NumColor,$E$2:$E$20).

Note: In both formulas, $F$2:$F$20 is the Color column, NumColor is the specified named range, $E$2:$E$20 is the Amount Column, and you can change them as you need.

Now you will see the cells in the Amount column are counted and sum by their fill colors.


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


Supposing the colored cells scatter in a range as below screenshot shown, both above methods cannot count or sum the colored cells. Here, this method will introduce a VBA to solve the problem.

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 background 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:
A. Count the colored cells: =colorfunction(A,B:C,FALSE)
B. Sum the colored cells: =colorfunction(A,B:C,TRUE)

Note: In above formulas, A is the cell with the particular background color you want to calculate the count and sum, and 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 Functions

Kutools for Excel also supports some useful functions to help Excel users to make special calculations, says count by cell background color, sum by font color, etc.

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the blank cell you place the counting results, and click Kutools > Kutools Functions > Statistical & Math > COUNTBYCELLCOLOR. See screenshot:

2. In the Function Arguments dialog, please specify the range you will count colored cells within in the Reference box, choose the cell that is filled by the specified background color in the Color_index_nr box, and click the OK button. See screenshot:

Notes:
(1) You can also type the specified Kutools Function =COUNTBYCELLCOLOR($A$1:$E$20,G2)  in the blank cell or formula bar directly to get the counting results;
(2) Click Kutools > Kutools Functions > Statistical & Math > SUMBYCELLCOLOR or type =SUMBYCELLCOLOR($A$1:$E$20,G2) in the blank cell directly to sum cells based on the specified background color.
Apply the COUNTBYCELLCOLOR and SUMBYCELLCOLOR functions for each background color separately, and you will get the results as below screenshot shown:

Kutools Functions contain a number of built-in functions to help Excel users calculate easily, including Count / Sum / Average Visible cells, Count / Sum by cell color, Count / Sum by font color, Count characters, Count by font bold, etc. Have a Free Trial!


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 - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now

1. Select the range that you want to use, and click Kutools Plus > Count by Color, see screenshot:

2. And in the Count by Color dialog box, please do as below screenshot shown:
(1) Select Standard formatting from the Color method drop down list;
(2) Select Background from the Count type drop down list.
(3) Click the Generate report button.

Note: To count and sum colored cells by specific conditional formatting color, please select Conditional formatting from the Color method drop down list in above dialog, or select Standard and Conditional formatting from the drop down list to count all cells filled by the specified color.

Now you will get a new workbook with the statistics. See screenshot:

The Count by Color feature calculates (Count, Sum, Average, Max, etc.) cells by background color or font color. Have a Free Trial!


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 includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
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.
    Sunil · 3 years ago
    Yes it has worked and very good. but what about if I have to change the color or reduce the colour cells or increase the cell , the formula don't increase or decrease accordingly as like in pivot we refreseh the pivot and sheet get update according to change in main data.
  • To post as a guest, your comment is unpublished.
    Shaun · 4 years ago
    wow, got this working but have an issue. I can get the formula to work on the same tab; but it will not work when looking up colours on a different tab. The other tabs contain colours that are generated by conditioning format; has any one got any advice
  • To post as a guest, your comment is unpublished.
    Teoh · 4 years ago
    Thanks for the tip. Very useful. Keep up . Good stuff. Well done : )
  • To post as a guest, your comment is unpublished.
    Iton · 4 years ago
    Hi, I tried copying the VBA-formula as indicated above but when trying to apply the formula [i]ColorFunction[/i] I simply get "#NAME?". I am using Excel 360. Can it be that the VBA-formula does not work on this Excel-version? Could someone else please have a try?
  • To post as a guest, your comment is unpublished.
    S.mantha · 4 years ago
    Well, added this in as a macro. Some problem with the "v=result" line, excel will not let me enter it. Copied exactly as instructed. Didn't save it, ran it anyway in Excel just to test it. Of course it didn't work. Should I delete the spacing in the formula above? Are the steps supposed to be numbered? (they are not currently).
  • To post as a guest, your comment is unpublished.
    Nopchanok · 4 years ago
    It's really work for me, i use 2007. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Appie · 4 years ago
    Hi, Tried to apply this on one of my sheets with an SQL connection that is stored in excel as table query with name range Table_query. I can see the CF's applied in my table but the count always results in 0.

    =colorfunction(A37;Table_Query;FALSE)

    A37 contains the color

    any ideas?
    Cheers,
    Appie
  • To post as a guest, your comment is unpublished.
    Appie · 4 years ago
    Hi, Tried to apply this on one of my sheets with an SQL connection that is stored in excel as table query with name range Table_query. I can see the CF's applied in my table but the count always results in 0.

    =colorfunction(A37;Table_Query;FALSE)

    A37 contains the color

    any ideas?
    Cheers,
    Appie
  • To post as a guest, your comment is unpublished.
    Appie · 4 years ago
    Hi Guys,

    Thanks for this wonderfull module. I applied it and saved. Funny thing is that it returns 0, i'm applying this on a named_range large table of 50.000 rows i can see the colors in the table but nothing happens in the cell that niets to count it other than 0.

    Any help would be appreciated.

    Appie
  • To post as a guest, your comment is unpublished.
    Jvnto · 4 years ago
    Hi, I have a doubt!. If I will have a range for example "$A$1:$A$10", and:
    A1(color black), A2(color blue), A3, (color yellow), A4 (color white), A5 (color white), A6 (color yellow), A7 (color white), A8 (color white), A9 (color blue), A10 (color yellow).
    How I can add some cells of the same color with a macro, for example:
    A1 = SUM(A2,A9) , A3 = SUM(A4,A5) , A6 = SUM(A7,A8) , A9 = SUM(A10).
    • To post as a guest, your comment is unpublished.
      Appie · 4 years ago
      Hi, Tried to apply this on one of my sheets with an SQL connection that is stored in excel as table query with name range Table_query. I can see the CF's applied in my table but the count always results in 0.

      =colorfunction(A37;Table_Query;FALSE)

      A37 contains the color

      any ideas?
      Cheers,
      Appie
  • To post as a guest, your comment is unpublished.
    Usman · 4 years ago
    Thanks a million! The color function works perfectly!
  • To post as a guest, your comment is unpublished.
    Tim Nevins · 4 years ago
    Count by color function worked perfectly with just a copy/paste. I've been fooling with this for a couple of hours. Wish I'd found this site first.
  • To post as a guest, your comment is unpublished.
    Muhammad Faizan · 4 years ago
    Thankyou. very helpful information
  • To post as a guest, your comment is unpublished.
    Abdul Rashid · 4 years ago
    Thanks Sir,
    This is a wonderful work and helps me a lot.
  • To post as a guest, your comment is unpublished.
    Harpreet Singh · 4 years ago
    its done, But when we clear the cell from color the number of counting did not down
  • To post as a guest, your comment is unpublished.
    Harpreet Singh · 4 years ago
    Its Done, But when we clear the color cell number of counting of colors can't up or down
  • To post as a guest, your comment is unpublished.
    Tracy · 4 years ago
    This was very helpful. Thank you. :-)
  • To post as a guest, your comment is unpublished.
    Moataz · 4 years ago
    Hi,
    it's great, but why it's not working automatically?
    is it possible to make it working automatically?
    i hop that, thanks.
  • To post as a guest, your comment is unpublished.
    Arpad Kegl · 4 years ago
    :lol:
    very usefull
    thanks a lot
  • To post as a guest, your comment is unpublished.
    Nitin Verma · 4 years ago
    Dear Friends,
    Pls do me favour, if u have Kutool activation key or activator..pls send me on this mail id .
  • To post as a guest, your comment is unpublished.
    Bayar Faisal · 4 years ago
    Very useful article, thank you a lot.
    Regarding the VBA code, what if I have merged cells?
    I have 62 total, but some of the are merged, my total colored are 27, but I want them to be six as they are merged.
  • To post as a guest, your comment is unpublished.
    Cris · 4 years ago
    Extremely useful. Thanks for sharing.
  • To post as a guest, your comment is unpublished.
    Luke Newham · 4 years ago
    Hi There,

    Many thanks for this - I am reporting on cell colours and the above has been useful. However, I am facing a problem whereby when I automatically colour cells using conditional formatting, the above code is not recognising those cells as being coloured. Is this something that can be catered for?
  • To post as a guest, your comment is unpublished.
    Sankara · 4 years ago
    hi i want to add cells say A1 Sankar 130,A2 Vibav 230 I have higlighted the text as blue and green,when i give the above function its showing !value.how can we add only numbers highlighted by neglecting it
  • To post as a guest, your comment is unpublished.
    Martin Pryce · 4 years ago
    Thanks for this - extremely helpful and straightforward to follow.
  • To post as a guest, your comment is unpublished.
    hoori · 4 years ago
    Thanks. It works the best and it saved me :D
  • To post as a guest, your comment is unpublished.
    sokly · 5 years ago
    how to run code in this page
  • To post as a guest, your comment is unpublished.
    Vim · 5 years ago
    For some reason the UDF returns #NAME when I enter the formula. Any ideas on why that could be be happening? I inserted the module and pasted the code above. Then saved and went to file-> Close and return to Microsoft Excel.
  • To post as a guest, your comment is unpublished.
    Rem · 5 years ago
    Hey, quite usefull the count fucntion but now I'd need to add a data criteria.
    I would like to count a cell colored only if a data is in it. So if not data in a colored cell, do not take it in account. How can it works?
    Thanks
  • To post as a guest, your comment is unpublished.
    JoeG · 5 years ago
    Amazing thank you!

    If there was an addition for the formulas to update automatically when the source data changes that would be totally amazeballs, but otherwise it's pretty cool :))
  • To post as a guest, your comment is unpublished.
    Mike N · 5 years ago
    I entered the function and it works great! Only issues is the doc is very slow when using basic functions like a filter. it's only 27kb. Could you advise a solution to this? Thanks!!
  • To post as a guest, your comment is unpublished.
    WAQAS · 5 years ago
    Remarkable
    Very helpful
    continue your good work
  • To post as a guest, your comment is unpublished.
    Anon · 5 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 · 4 years ago
      Thank you, this saved the day for me!
  • To post as a guest, your comment is unpublished.
    Waqas · 5 years ago
    Really helpful. Thank you.
  • To post as a guest, your comment is unpublished.
    Ashish · 5 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 · 5 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 · 5 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 · 3 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.
  • To post as a guest, your comment is unpublished.
    Bmetz · 5 years ago
    Nice, useful function! The colorfunction works as expected on a large worksheet that enables not having to manually calculate entries after color format changes. :-)
  • To post as a guest, your comment is unpublished.
    Thirumalraj · 5 years ago
    Very Helpful...thanks a lott :)
  • To post as a guest, your comment is unpublished.
    Tiny · 5 years ago
    how do you calculate average using this?
  • To post as a guest, your comment is unpublished.
    Sree · 5 years ago
    Thanks a ton. This is awesome.
  • To post as a guest, your comment is unpublished.
    Srinu · 5 years ago
    Hi ,i have applied conditional formatting for color coding ,now i need to count & sum the colored cells ,plz help
  • To post as a guest, your comment is unpublished.
    Nik · 5 years ago
    Wonderful bit of code - thanks so much!
  • To post as a guest, your comment is unpublished.
    Manivannan · 5 years ago
    Awesome... It is really help and very easy to use... Thank you!
  • To post as a guest, your comment is unpublished.
    Kelvin · 5 years ago
    Hi,

    Thank you for sharing the codes on count by colours in such a clear and simple way.
  • To post as a guest, your comment is unpublished.
    traeden · 5 years ago
    Hi,

    copy and pasted the formula directly. When I try and use function I get a dialogue box that says "compile error: Expected: end of statement" it also opens VBA and highlights the word "Dim" "Optional SUM As Boolen)[u]Dim[/u]" in the code you pasted.

    Thanks,
  • To post as a guest, your comment is unpublished.
    nikhil · 5 years ago
    i have used conditional formatting to colour certain cells based on a set of rules. however i do not know how it colours the cell as the background colour of the cell is still white even though the cell is the required colour.

    I was hoping you would be able to adapt the function for cells coloured using conditional formatting
  • To post as a guest, your comment is unpublished.
    Fany · 5 years ago
    Hi, thanks for this post. I tried to apply the formula and gives me just 1 as a result... don't know what Im doing wrong
  • To post as a guest, your comment is unpublished.
    Amar · 5 years ago
    Thanks.. this formula tutorial was very helpful !!
  • To post as a guest, your comment is unpublished.
    Melanie · 5 years ago
    Thank you so much, this is awesome!
  • To post as a guest, your comment is unpublished.
    Hilary · 5 years ago
    Still didn't work for me :(It worked great in the beginning when I first apply the codes, but it would not calculate again when I reopen the file. I've already saved it in "Excel Macro-Enabled Workbook" but still wouldn't work. :( Is there anything I'm doing wrong?