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 15. Total comments: 838

  1. 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.

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

  3. 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

  4. 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.

  5. 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

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

  7. 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.

  8. 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.

  9. Hi,

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

    Thanks.

  10. 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

  11. 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?

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

  13. 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!!

  14. 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....

  15. 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

  16. 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

  17. 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?

  18. 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.

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

  20. 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!!!

  21. 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.

  22. 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.

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

  24. 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

  25. 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!

  26. 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

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

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

  29. 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.

  30. I cannot get the workbook sum function to work

  31. To count cells by colour on the same worksheet, Microsoft recommends a macro based on just 10 lines of code, compared with you umpteen lines. Is there a special reason to your code?

    1. I just noticed that the MS code just does a count, whereas your code sums as well. Apologies for my misunderstanding.

  32. Thanks for the great article! I am working on a duel scoring system where each has a numerical score (1-5) and a traffic light score (by filling in the cell colour with red, Amber or green) the two scores are not related. Is it possible to modify the code so that it calculates the average score and fills in the cell colour according to the agreement of the traffic light score (all green = green, all red = red, anything else = Amber)?

  33. Hi,

    Great stuff.. have learnt a lot for your website.

    Need one help regarding sum of cells based on color formatting. The code provided is macro, how to convert this to a function so that I can use it as and when needed

    Thanks,
    Prasad

  34. i can not able to see color and sum by color in an Excel worksheet.so i need help from you.please explain it brief

  35. Thank you, worked great

  36. I've seen the question I have a few times but there is never an answer associated. I run reports for my boss and some of them require totaling by sum and count for programs during the month based on conditional formatting. The formal works in that I can see the count and sum but, a) I need to include the numbers in my report and b) the count and sums are in separate boxes. Is there a way to populate the answer into a cell with the needed count or sum based on conditional formatting rather than a pop up box which doesn't work for my boss when I print the report.

  37. Im using Excel 2013. What is the formula to count the cell color of specific information within the cell. EXAMPLE: I have APPLES(red), GRAPES(green) and STRAWBERRIES (red). I want to return the count of cells colored red that are STRAWBERRIES only, not all cells colored red.

    1. Hi Carl, I'm needing the exact same info! I'd like to count by cell color, IF a specific text criteria is met (just like you said, Cell is RED and contains text "STRAWBERRIES"). I do a lot with data, and automating things like this would be a huge help. I've tried combining formulas and although they don't error out, I'm not achieving the response I need. Does anyone know if there is a solution?

  38. Hi,

    I'm using the color count for conditional formatting Excel 2010. How can you have the count be placed in a cell automatically after entering data instead of having to run the rule and only getting the result in a pop-up box?

  39. Hi,

    Your example is good one.

    I am in the same problem, but what about merged cell. How do I count merged cell fill with specific color?

    regardsd,

    Santosh

  40. I followed the instructions verbatim for counting cells that contain a color and it didn't work. When I try to use the formula, Excel returns #NAME?
    I did save the spreadsheet as a macro-enabled spreadsheet. I even went back into the VBA editor to make sure the code was still there and it was. (and I had copied the code verbatim by selecting it from this website and pasting it.)

  41. hi,

    how to count the date between one week or two weeks.

    for example in cell A1 is date. I want to count the date after one week( between 2 to 7weeks).what is the formula I can apply. I got the formula to count for today's date or overdue date countifs(a1:a10,"<=&today(),b1:b10,d15), but I cannot get the formula to count the date in between one week.
    1/8/16
    2/8/16
    3/8/16
    8/8/16
    11/8/16
    12/8/16
    15/8/16
    17/8/16

  42. This is a fantastic tip !

    Thankyou so much :)

  43. Hi Please help me with how i can use indirect formula in excel 2010 because when i m trying enter formula i am getting following error #REF!
    I am entering formula indirect(select cells) but i am not getting when i am entering formula like this =indirect("Select") i am getting the outcome but not able to use for multiple cell and sheets need your help to get the indirect formula in excel 2010

  44. This macro for "How to count by color and sum cells colored using conditional formatting" is a big help. Well done. I was wondering if there is a way to take the output that pops up in the Message Box and have that info put into a cell. For instance, the Count is what I am really looking for. Is there a way to have the Count displayed in a cell for the range selected instead of a popup window?

  45. You guys are awesome !! It works :)

  46. Hi,
    Am running Macro with cells that have both hyperlinks to server as well as conditional formatting of them cells. Have copied the codes from above script (CountCellsByColor in addition to the Conditional Formatting code later in the page) and they fail to capture the cells which are conditionally formatted, returning a result of '0'.
    Moving the data selection area outwith the hyperlink/formatted area returns valid sum data just cant seem to get the Conditionally Formatted cells to be recognised. Ground to a halt here, help...

  47. Please ignore my previous post I finally figured out my blunders.

  48. I see that the code works for everybody but me, so it must be me, but would still like know why it doesn't work. The error on the worksheet in cell F2 is #Name?. I first hand copied from the web site the code, then hand typed it in, still the same error. Then in cell F2 , I hand typed the formula, that was said to type. =CountCellsByColor(A1:A10,E2), where E2 is the cell that has the color I'am trying to count. I work on Excel 2010, and Windows 7. Looking for how many yellow cells there are.

    1. Steve Martin, you are not the only one who is having the problem. I am glad you figured it out. Could you please share what was wrong. I am working in Microsoft Excel for Mac v15.28. I entered pasted the code into the vbe, then added the formula as was specified, and I also received the #NAME? reply in the box with the formula. I have no idea what I did wrong. any help would be greatly appreciated.

      1. If you are saving the macro in your personal workbook, the name of the function will be PERSONAL.XLSB!CountCellsByColor, so you will get a #NAME error if you just put CountCellsByColor (if you save it just to the workbook, this name should work just fine)

  49. Thanks for sharing, I have certain merged cells to count in between the range. is there a solution yet for that

  50. Great Stuff. Thanks for sharing.

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 :)