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

or

Kutools for Excel 22.00 HOT

300+ Powerful Features You Must Have in Excel

Kutools-for-Excel

Kutools for Excel is a powerful add-in that frees you from performing time-consuming operations in Excel, such as combine sheets quickly, merge cells without losing data, paste to only visible cells, count cells by color and so on. 300+ powerful features / functions for Excel 2019, 2016, 2013, 2010, 2007 or Office 365!

Read More Download Buy now

Office Tab 14.00HOT

Adding Tabbed Interface for Office

Office Tab

It enables tabbed browsing, editing, and managing of Microsoft Office applications. You can open multiple documents / files in a single tabbed window, such as using the browser IE 8/9/10, Firefox, and Google Chrome. It's compatible with Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365. Demo

Read More Download Buy now

Kutools for Outlook 12.00NEW

100+ Powerful Features for Outlook

Kutools-for-Outlook

Kutools for Outlook is a powerful add-in that frees you from time-consuming operations which majority of Outlook users has to perform daily! It can save your time from using Microsoft Outlook 2019, 2016, 2013, 2010 or Office 365!

Read More Download Buy now

Kutools for Word  9.00NEW

100+ Powerful Features for Word

Kutools-for-Word

Kutools for Word is a powerful add-in that frees you from time-consuming operations which majority of Word users have to perform daily! It can save your time from using Microsoft Word / Office 2019, 2016, 2013, 2010, 2007, 2003 or Office 365!

Read More Download Buy now

Classic Menu for Office

Bringing Back Your Familiar Menus

Restores the old look and menus of Office 2003 to Microsoft Office 2019, 2016, 2013, 2010, 2007 or Office 365. Don’t lose time in finding commands on the new Ribbon. Easy to deploy to all computers in enterprises and organizations.

Read More Download Buy now

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.
    Dean · 5 years ago
    Excellent function, worked first time. Thank You.
  • To post as a guest, your comment is unpublished.
    Ahmad Raza · 5 years ago
    Excellent function. It works for me. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Jamie · 5 years ago
    Keeps saying ambiguous name detected:ColorFunction

    Sorry very inexperienced at excel

    But thanks for any help
    • To post as a guest, your comment is unpublished.
      Cindy · 5 years ago
      Yes - I'm having same problem. I've saved as macro enabled, exited and came back in and enabled macros. I'm trying to incorporate macro into existing worksheet and keep getting "ambiguous" error. Doesn't seem to work for existing spreadsheets.
  • To post as a guest, your comment is unpublished.
    Hassan · 5 years ago
    Man i fucken love you. God bless you
  • To post as a guest, your comment is unpublished.
    Manish · 5 years ago
    Great Tool. However, can any one help me to exclude the blank cell from the count formula.
  • To post as a guest, your comment is unpublished.
    Dileepa · 5 years ago
    Works perfectly.. Thanks a lot
  • To post as a guest, your comment is unpublished.
    Benjiman · 5 years ago
    I second Tyron's request. I need this colorfuntion to update automatically when the source data is changed.
  • To post as a guest, your comment is unpublished.
    Tyron · 5 years ago
    Awesome! Works like a charm.

    Two items to add:

    1. A way to update when cell colors change - from what I see here it looks like I will need to have an additional macro button to update the calculation.

    2. A way to calculate cells whose color changes based on conditional formatting.

    If it had both these two elements it would be a golden piece of code.
  • To post as a guest, your comment is unpublished.
    faizal · 5 years ago
    Excellent work.. Thanks :)
  • To post as a guest, your comment is unpublished.
    Pete · 5 years ago
    Awesome, thanks so much for this VB code, works a charm.
  • To post as a guest, your comment is unpublished.
    rogsh · 5 years ago
    GREAT, two tomb, be invaluable aid
  • To post as a guest, your comment is unpublished.
    Gabe · 5 years ago
    That works. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Brittany · 5 years ago
    I might've missed the instructions in the thread for this, but how do you get it calculate automatically ?
  • To post as a guest, your comment is unpublished.
    Caio Alve · 5 years ago
    Thank you very much.
  • To post as a guest, your comment is unpublished.
    Joe jamies · 5 years ago
    Very helpful indeed. Thumbs up! :lol:
  • To post as a guest, your comment is unpublished.
    Appaswamy · 6 years ago
    itsVery good.... and useful
  • To post as a guest, your comment is unpublished.
    tim · 6 years ago
    OK folks here is a very easyway I discovered that will allow you to count the number of cellls in a spreadsheet that are say RED. Rather than just set the cell background to red also place a red r in the cell and then format the cell background red and you will not see the red r but its there.
    You then set a countif argument...=COUNTIF(H7:H11,"r") to capture the number of values r in cells h7 through h11.....very simple but works very very well.
  • To post as a guest, your comment is unpublished.
    Stephanie · 6 years ago
    Very clear instructions, thank you :)
  • To post as a guest, your comment is unpublished.
    Matt_Priebe · 6 years ago
    This worrked great on the first shot. How do I set it up so if you change a colour it automatically regenerates?
  • To post as a guest, your comment is unpublished.
    safa · 6 years ago
    very nice tool, thanks from the bottom of my heart. May god bless
    U
  • To post as a guest, your comment is unpublished.
    Trish · 6 years ago
    Thanks this is exactly what I needed.
  • To post as a guest, your comment is unpublished.
    shahid ashraf · 6 years ago
    thank man very help ful job, God bless u
  • To post as a guest, your comment is unpublished.
    jeffshieldsdev · 6 years ago
    Leena, see my comment above.

    [url]http://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html#comment-730[/url]
  • To post as a guest, your comment is unpublished.
    Leena · 6 years ago
    For some reason the formula seems to be considering some colors the same - for example when I enter a formula to count all the green cells, it counts both green and red cells, and vice versa. It only seems to be happening with some colors, while others work just fine.
  • To post as a guest, your comment is unpublished.
    timbo · 6 years ago
    SOrry if this has been asked before but how do I do a countif in the colour of a value using HUtools or not...???????
  • To post as a guest, your comment is unpublished.
    Bobbyb · 6 years ago
    Thank you!!. Love this function!!!!
  • To post as a guest, your comment is unpublished.
    jeffshieldsdev · 6 years ago
    You might want to use the Color property instead of ColorIndex, as it'll be more precise. For instance, I just found a light red and light green colors both return a ColorIndex of 19, but their Color property returns 14474738 and 14545386, respectively.
    • To post as a guest, your comment is unpublished.
      Adele · 6 years ago
      Jeff I have changed the wording Colorindex to ColorProperty and now it returns #VALUE. Any ideas what I have done wrong?
  • To post as a guest, your comment is unpublished.
    Swindle · 6 years ago
    Quick question, how do you count and sum cells from other worksheets? The summing functions don't work because they are not part of the colorfunction module. Is there a work around? Any help would be appreciated.
  • To post as a guest, your comment is unpublished.
    Tricky · 6 years ago
    I am still struggling to resolve my problem.
    I need to count the number of cells within a range of cells which....
    1. contain text which match the text in a particular cell,
    AND
    2. has a background colour that match another particular cell.
    I need the counting to be automatic as I change the text or colour of the cells within the range of cells.
    Can anyone lease help?
  • To post as a guest, your comment is unpublished.
    Beefmitten · 6 years ago
    Brilliant, Exactly what I was looking for.
  • To post as a guest, your comment is unpublished.
    Ardhi · 6 years ago
    It works like charm !!! Thank you very much !!
  • To post as a guest, your comment is unpublished.
    Tomas · 6 years ago
    Thanks, a perfect hint! :-)
  • To post as a guest, your comment is unpublished.
    Ahsan · 6 years ago
    Hi,
    If I change the cell color using another formula and then ask to calculate sum up with the above formula then it doesn't work. Can you please help me resolving the problem?
  • To post as a guest, your comment is unpublished.
    Josh · 6 years ago
    I am not sure if this question has already been asked, but how do you use the colorfunction when looking to find the sum of colored cells across many worksheets within the same Excel file? Thanks.
  • To post as a guest, your comment is unpublished.
    Brenda · 6 years ago
    Very Helpful. Thank you for sharing!!! :-)
  • To post as a guest, your comment is unpublished.
    Madhuka · 6 years ago
    Thanks and very useful !
  • To post as a guest, your comment is unpublished.
    Modeste · 6 years ago
    I am trying to add this macro to an exisiting excel macro enabled worksheet (.xlsm). However "colorfunction" is not recognized (pop up "Ambiguous Function" comes up)when I try to add it to a separate worksheet in the exisiting .xlsm file. It does work when I add it to a new file from scratch but not when I try to incorporate it in an exisiting .xlsm file. Is there something I'm missing? Please assist.
  • To post as a guest, your comment is unpublished.
    pama · 6 years ago
    Thank you very much. Easy to set up and it works!
  • To post as a guest, your comment is unpublished.
    cseinstein · 6 years ago
    This may be too far outside the purview of this post, so ignore if so. But, I wonder whether it is possible to use this function (the User Defined one) only on cells of a certain range (in my case, filled with values of 1-8, ignoring higher numbers). I can clarify with more information if requested.
  • To post as a guest, your comment is unpublished.
    Bing · 6 years ago
    Hi, I am using VBA to add up colored cells in a spreadsheet and all is working well. If I change the cells and update (+40) like this =20+30+40, then the total changes to #name? I have tried reloading the module, enter on the code, nothing fixes it. Basically any changes I make to the cells being counted, wrecks my sum of colored cells. Any help?
    • To post as a guest, your comment is unpublished.
      Ashley · 3 years ago
      I have had the same issue. The VBA works perfectly a few times and then it stopped after I closed out, restarted, and then opened. I tried deleting and reloading the macro but it still won't work.
  • To post as a guest, your comment is unpublished.
    vikram · 6 years ago
    fantastic. thank you so much for this informative post.
  • To post as a guest, your comment is unpublished.
    Mar · 6 years ago
    Hello, how do you get the median of specific colored cells with User Defined Functionand mode?
  • To post as a guest, your comment is unpublished.
    Kristi · 6 years ago
    Hello.
    How can I save this code and apply the following formula?
  • To post as a guest, your comment is unpublished.
    Matus · 6 years ago
    Hello, one question:
    If I have in yelow cells for example dates and I want to count these yelow cells between 1.1.2014 and 1.2.2014?
    How it should looks like?
    Thank you...
    • To post as a guest, your comment is unpublished.
      Admin_jay · 6 years ago
      Hello, you can try to use this feature to select those cells first. Please go to this link for more information about Select specific cells http://www.extendoffice.com/product/kutools-for-excel/excel-select-specific-cells-rows.html

      And then please apply count by color.

      :-)
  • To post as a guest, your comment is unpublished.
    Tricky · 6 years ago
    Brilliant. Thank you.
    BUT
    can I ask if this can be taken a step further....
    I want to look at a range of cells and only count the number of cells which contain certain text (e.g 2 letters)AND are filled with a certain colour.
    Can you help?
  • To post as a guest, your comment is unpublished.
    Vimal Nakum · 6 years ago
    Very useful. It works!

    Thanks a lot
  • To post as a guest, your comment is unpublished.
    Md. Abdullah Sarker · 6 years ago
    Thanks a lot. its working.
  • To post as a guest, your comment is unpublished.
    Rachael · 6 years ago
    Thanks a lot. It was really helpful. Now I have a very helpful and report-able expenses spreadsheet. Thanks again.
  • To post as a guest, your comment is unpublished.
    Md. Abdullah Sarker · 6 years ago
    Hi
    This is Abdullah Sarker.I faced one problem in excel.Please suggest me.
    Problem: How to count Or Sum Only color cell in excel.
  • To post as a guest, your comment is unpublished.
    tayyab · 6 years ago
    Perfect!! n very helpful,

Feature Tutorials