Comments on: How to sum and count cells by color in Excel

Even though Microsoft Excel has a variety of functions for different purposes, none can count or sum by color of a cell. Aside from third-party tools, there is only one possible solution - create your own functions. Continue reading →

Comments page 14. Total comments: 838

  1. Hi,
    this is amazing tool, but I have a little problem. If I want to use a conditional formatting, the code doesn't work. The font is changing to red via conditional formatting, but the code sees it like black. After I change color of font handly to red, everything is OK.
    Can you help me please? Thank you very much.

  2. the code is not working.
    my cell shows #NAME?
    its an error. can u rectify it?

  3. you are fabulous! Thank you so much!

  4. Hi, But what if I'd like to count if & color? Can I combine Countifs and CountCellsByColor?
    Example order date 20-Okt-13 (column B) & Red (Column F)?

  5. I just want to say thank you VERY much for this easy to use and detailed tutorial! Top quality! I am bookmarking your site for future use! Great job!!

  6. Thank you, thank you, thank you! This worked out perfectly!!

  7. I love this formula, but I am struggling getting it to work in an IF function.

    If Column I = 8 (or G, or L) and Column B is highlighted yellow (or red or green) how many cells are highlighted yellow?

    Here is my current formula for I=8, B=yellow

    =if('RawData'!$I$1:$I$500=8,(countcellsbycolor('RawData'!$B$1:$B$500,Counts!G3)),"")

    Thank you!

    1. Hello, Margaret,

      Unfortunately, your task can be solved only by changing the work of the CountCellsByColor function in your VBA macro.
      Since we do not cover the programming area (VBA-related questions), we can't help you with this. I can advise you to look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  8. I love this formula, but I am struggling with running an "If" Formula with it so I can use 3 basic colors instead of 9 (or potentially more).

  9. This code worked fine, but it made inputting data on my spreadsheet incredibly slow, everytime I type something Excel freezes and it takes at least 5 seconds so I can type again. And I don't even have that many lines: 600 at the moment.

    Am I doing something wrong or is this code that poorly optimised?

  10. This code is great. With the count on conditional formatting why when running the macro the results are not show in the spreadsheet? Is that possible?

    thanks

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  11. I have a problem with the formula SumCellByFontColor.
    It doesn't sum the correct number. Meaning the correct amount is 5122,45 and it sums up 5121,00. Cell range is right. Decimals are correct. Am I suppose to do something else to correct this?
    Thanks in advance.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  12. thank u

  13. Thanks for the guide. It does however have limitations which is a shame. In my case, I have an excel spreadsheet with conditional formatting, so if something is a year out of date for instance, the cell fill colour will go to red. However, whilst the colour is displayed, excel actually lists the no fill for the cell colour within the font ribbon.

  14. Thank you for this code. I have used it to count cells by their conditional formatted colour. What I would like to do is push this a step further and be able to define the range of cells to count and also the colour to look for before having the result entered into a cell for further calculating to be done with it. Any idea how to do this?

  15. Thanks and really appreciate your efforts. Liked your code for the Count of cells through Conditional formatting. Is it possible to display the results in excel cells also?

  16. Please disregard previous question (#256), when copy/paste from latest file worked fine (for some reason could not from instruction page). Thanks for solution - much appreciated.

  17. Probably doing something wrong. However, when trying any of the functions receive #VALUE! error (when stepping through, appears to occur immediately). Any suggestions? Thanks

  18. Hi,

    thanks for the guide. It does however have limitations which is a shame. In my case, I have an excel spreadsheet with conditional formatting, so if something is a year out of date for instance, the cell fill colour will go to red. However, whilst the colour is displayed, excel actually lists the no fill for the cell colour within the font ribbon.

    1. Hi,

      Ive now seen the conditional format section on the above article. It can be used to count numbers by running the script, but does not generate any summary within excell that can be used. Is there a way of doing this?

      Thanks

  19. Same for me in excel 2016, "the formula type contains an error". The debugger highlights: Function SumCellsByColor(rData As Range, cellRefColor As Range).

    1. Hello,

      if you still need assistance with your task, could you please send us your workbook to support@ablebits.com? Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.
      Don't forget to link this comment in your message.

      Our technical specialist will take a look at your task and try to help.

  20. Hi there, working on Excel 2016. Whenever I copy and paste formula after inserting module entered. The message appears "the formula type contains an error". The cells all have wording in which is why I utilised the first module above.

  21. Great routines!!!
    BTW, given that some of them helped me, I generated a new function called "SumProductCellsByColor(Range 1 column, cell with color pattern, colum 2)". So, if someone want to take advantage of this code please send me an email I will share it.

  22. Excelent Tool I was loocking for that since many days ago and now I got it thanks for post that friend

  23. FORMULA UPDATING? PLEASE TRY & SEND AN ANSWER TO THIS ONE :-):
    I emailed 7 hours ago that the AbleBits method shown for counting colors was the very solution to the problem I was having. I have just noticed something, even so, and I therefore have a question:
    I seem to need to go into edit mode (F2) with some cell or other, after a change to the colour of a cell has just occurred, in order for the formula to update. Otherwise I may have just created, say, 2 cells with a particular font instead of 1, and the count has been saying 1. I find that the count number (1) does not update immediately to 2 - not until I edit and enter some cell or other. That brings about some updating across the spreadsheet which includes this UDF. Whereas an 'ordinary' formula will update as soon as the data, on which it is based, has been altered. How can I get around that? Hope you can help me here, thanks.
    LOTUSMAN

  24. Very pleased with this material on how to count numbers of cells with certain colours, and that's all I want to report here. I nearly always would enter a code, like 1 or 2 to a cell to record its category and use countif, but a particular file I am working on needs to be more visual and has a macro button or two to 'click' certain colours into cells, so when I needed to count them up I was stuck. But not now! Thank you.

  25. Hi there,
    I have been trying to use the code and I think the VB part is ok. However, when I try to type in the function I get an error as I enter the criteria section. for example =countbycellcolor($c$33:$c$66,A5) excel gives me an error when I click on the A5 and says this format is not correct. I use excel 2013

  26. Thank you so so much! It works perfectly!

  27. Just having a bit of trouble running the SumCellsByFontColor Macro. I'm hoping someone can give me a tip here; I'm new to Visual Basic.

    Whenever I run the Macro, I get a "Compile Error, Syntax Error" with the "Dim indRefColor As Long" highlighted in Blue. (I can send a screenshot if that works better.)

    In all cases, I have entered only cut'n'paste data from that shown above - nothing entered manually.

    What am I doing wrong. I could easily believe I'm leaving out a step or two.

    Thanks.

  28. Hello Team,

    I am looking solution of my problem. is there any vba which change subject date and body date automatic in outlook13. i used to send daily sales report on daily basis so i am looking any code which automatic change date to today()-1.

    1. Hello.

      Thank you for contacting us.

      We are always ready to help you, but we do not cover the programming area (VBA-related questions).

      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry we can't assist you better.

  29. Hi,

    The VBA worked wonderfully. That's a smart coding.

    Thanks.

  30. Quick question... Just wondering why this code was made so difficult and mucky... for example :

    Function CountColors(Check As Range, Base As Range) As Long

    For Each i In Check
    If Base.Cells(1, 1).Interior.Color = Check.Interior.Color Then: Amount = Amount + 1
    Next i

    CountColors = Amount

    End Function

    Works exactly the same way...

    1. Had that backwards ;)

      Function CountColors(Check As Range, Base As Range) As Long

      For Each i In Check
      If Check.Cells(1, 1).Interior.Color = Base.Interior.Color Then: Amount = Amount + 1
      Next i

      CountColors = Amount

      End Function

  31. I would like to thank you for this wonderful tutorial. This has helped me tremendously. I currently use your code to count how many passes (green) and fails (red) i have on a particular column on a specific worksheet in my workbook. Once the data is populated, I use a pivot to auto update a chart for reference. I would really like to use conditional formatting on that column, so a if a user selected pass, it would automatically turn green, but when I do this, the code stops registering color. I've tried using the count by color and sum cells colored using conditional formatting but I think i'm doing something wrong. I can't get it to work. Can you assist?

  32. how to add cells having same background color across different workbooks

  33. Hi,

    Thank you so much for the code. It worked perfectly for me until I need the sum of cells in a certain font color while the background colors of these cells are different. It seems to be only adding the cells with the same background color as the cell that defines the font color, and leaving out those cells with a different background color. Anyone know how to solve this?

    Thanks in advance!!

  34. Hi Friend,
    I just want to know, hot to color the cells by using a single cell value.

    ie. - Cell one value - 20
    Want to fill specific color in next 20 cells....

  35. Hello,

    I am new to excel/vb/macros and I have an issue that I can't resolve. I've tried to manipulate your code but I'm messing it up without desired output.
    I want to check predefined range of cell for cells that have conditional formatting for background color (i.e. green) and list and highlight all green cells that don't have any value in them. I've found a code that is supposed to do almost exactly what I want but it doesn't work in my case - no results displayed. Your comments would be appreciated.
    Regards,
    Adam

    Sub Red_id()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim i As Integer
    i = 1
    Do Until i = 11
    If ws.Range("C" & i).Interior.Color = RGB(255, 0, 0) Then
    Debug.Print "C" & i & " is red!!"
    End If
    i = i + 1
    Loop
    End Sub

  36. Hi

    I love your code. Thanks for making it available.I am using a filtered list and have added the suggested modification from reply 32 so it only counts filtered lists (which is great and is working)

    It works but not quite for what I am trying to achieve.

    Its a school and its tracking grades for males and females. In effect I have one column where the autofilter can be M (for boys), F (for females), or both (for all students). I then have a second column where the colour coded grade is (it can be red, amber, green)

    What I want to do is to be able to filter by Boys for example, then count in the grade list all red grades, amber and green grades for boys. Then do the same for girls. Then just leave boys and girls ticked in the autofilter

    I guess what I am trying to do is to count all the red colours in my filtered list by girls and then in the same filtered list all the red ones by boys, then same for amber then green and display all in the same place the totals for each

    I have tried combining countcellsbycolor with IF statements, countifs, sumproduct and so on. But I cant get it to work right.

    What seems to happen mostly is that will boys and girls ticked, I am getting the count of all red boxes or boys and girls but not breaking them down into how many boys and how many girls. Same for amber, same for green.

    Please can you tell me if there is a way to combine countifs, if, sumproduct etc to count the red amber green by gender for the various combinations of just boys, just girls or both in the autofilter

    Can happily send spreadsheet

    Thanks

  37. I carefully followed the instructions in this post, but I cannot get the code to compile using Windows 10 Pro 64bit and Excel 2016. When I paste it into the VBA editor, many of the lines appear in red. When I try to run the code, I get a compile error that I cannot always clear.

    I can clear the lines that begin with Dim by removing the leading spaces, but others are more difficult or not possible for me to clear with what I know about VBA.

    Is this code still valid for the latest version of Excel 2016? Or, what am I doing wrong?

  38. Hi...

    I'm using a multiple data and to make it simple i'd prefer to use macro that can easily count the color, wherein if color green it gives 40 points but if it less than the desired target count of 5, it gives 0 point. I'm trying to use the CountCellsByFontColor and i got an answer, yet my data is complicated due to some conditions like sample below.

    Sample:
    Target: 99.30% yield
    Color Code if On-Target: Green Below Target: Red
    Target Attendance Performance at least: >=5

    Condition, yes you hit the target yield based on overall output and you got color green, but you're only 4 in attendance, so, instead of giving 40 points, you will receive 0 point.

    Thank you in advance.

  39. hi,
    i trying to use =CountCellsByFontColor(V3:V21;Q3)
    but this code dont work well if it count by horizontal

  40. Hi, I am doing a project. I have excel file with huge data. I want to add datas between two randomly placed data between two rows with same color or same name. I would appreciate any help!!!!
    Thanks!!!

  41. I got this to work perfectly on another spreadsheet. Now I'm trying to use it again and I'm getting an error.

    It says "Compile error: Sub or Function not defined."

    I'd appreciate any help.

    Thanks,
    Mike

    1. I have the same problem. See my post 236 below. I am beginning to think the code does not work with the latest version of Excel 2016.

    2. Sorry, I'm using the WbkSumCellsByColor function.

  42. Hi, can you tell me how to use the CountCellsByColor formula if the range isn't adjacent cells. For example, I want to count cells if a certain colour where the range is A1, D1, G1 rather than A1:G1?

    Thanks.

  43. Thank you! its really a bbbiiiiiigggg help! Works fine with me :)

  44. One simple update that might be useful in the original code is to skip over hidden cells, like those hidden by a filter. Here's what I did to CountCellsByColor:

    Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long

    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long

    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.Color And cellCurrent.Rows.Hidden = False Then cntRes = cntRes + 1
    Next cellCurrent
    CountCellsByColor = cntRes

    End Function

  45. Thank you for a great post. I was able to implement for a colleague in 5 mins (2007). Use of "key" or reference cell is great - no need to find the color code. Well done!

  46. I have set up an excel sheet tracking the performance of kids in sport. The columns in question return a result of the test in question. Based on the result, it will be colored, green, yellow, brown or red.

    I copied the text you recommended and put it into the Macros window as instructed. I then tried counting how many cells returning numbers with the different colors mentioned above, using the "CountCellsByFontColor" but I couldn't get it to work, regardless whether the text was bold or otherwise.

    can you help me with this

  47. Thanks mate :) this is very useful and helpful

  48. Count for colors works super slow but perfectly! Thank you so much.

  49. I tried your VBA code for counting conditionally formatted cells. I need this code to be a worksheet function. I have over 80 columns of data to apply the code to. It is not practical to select range and reference cell, the press Alt+F8 80 times.

  50. I cannot get the workbook sum function to work

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)