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

  1. Now, I am getting #NAME? in the boxes where my formula =CountCellsByColor(N102:N334,A335) was entered. Please Help!

    1. Hi Cassy,

      It's hard to say what exactly causes the error without seeing your workbook. If possible, you can send it to us at support@ablebits.com and I'm sure we'll be able to fixed.

  2. PS.. I have saved it as macro-enabled workbook

    1. Hi Khaled,

      The only possible reason that I can think of is that you accidentally added the code to a module of some other workbook. Will you check that please?

  3. thanks for the great support.. but i lose the function when i close excel and reopen it again.. any solution for that?

  4. It worked great but when I closed the page and opened it again, it gave me an error and the module was gone. I saved so i am confused on what is happening. Please help!

    1. Hi Cassy,

      Please make sure you save the workbook as macro-enabled and add the functions to the correct placement.

  5. very useful thanks much. Even though i dont try all as yet.

  6. This worked great! However, I want to do something a bit different and cannot find a solution. I just want to count any colored cell in a row. My rows are dates. The columns represent projects. The colored cells represent different tasks for these projects and the dates they are to be done. These tasks are color coded. I want to limit the concurrent tasks so I want to count number of color-coded tasks. I don't care how many yellow, how many blue, etc. I just want to know how many colored. For example, if there are three yellow, two blue and three orange, I want to see the number 8. Any ideas?

    Thanks,

    1. Hello Tim,

      You can achieve the desired result by counting the non-colored cells (white is also a color : ) and then subtracting this number from cells total. So, add the code to your workbook as described in the article and copy the following formula in the last column of each row, starting from row 2:
      =COLUMN()-1-CountCellsByColor(A2:Q2,R2)

      R2 - the cell with the formula, it should always be non-colored.

      A2:Q2 – color-coded columns that you want to count. If your color coded cells start from any column other than A, than replace -1 in the formula with the corresponding column number. E.g. if your color codes start with column B, the formula will be =COLUMN()-2-CountCellsByColor(B2:Q2,R2)

      1. You're the greatest, thanks!

  7. Hello, every time i attempt to use the sum by color function it says sum function not defined.

    My intended use is to mark a sales order size in red to indicate that it is complete. I do not want red in the total because this is primarily used for ordering more product and I do not want to over order. Thank you for any help!

    1. Hi RyanRo,

      This happens because Excel cannot find some or all of GetCellColor’s functions. Please check the most frequent causes I listed in my reply to comment 42 above.

  8. Hi, I am using the macro for cells that are conditionally formatted. Is there a way to produce the result in a cell versus a message box?

    Thank you for this information!

    1. Unfortunately, this cannot be done using a macro because of some limitations of Excel. We are planning to create an add-in that will cope with this. Please stay tuned!

  9. Thanks for the code. It works beautifully. I am wondering if there is a way to have the sum update automatically when you colour a new cell. I know you said above that it is not possible, but there must be some code that could be written that would allow you to be able to do it.

    I'm not very familiar with VBAs so I am not sure where to start. But couldn't you write a code of some sort that says:

    If "colour change" in "range" then "Run Function"

    Just wondering.

    1. Hello Darcy,

      We have found the solution on the page below and updated our script: https://sites.google.com/site/e90e50fx/home/sum-by-color-calculate-when-color-changed

      However, please not that this script works a bit slower. If it is not critical for you, proceed with the following steps:

      1. Download the updated code from the post.

      2. Create a new Class module and add the following code to it:

      Public WithEvents cX As CommandBars

Private Sub cX_OnUpdate()
  Static s As String
  Static l As Long
  If s = ActiveCell.Address(, , , True) Then
    If l <> ActiveCell.Interior.ColorIndex Then
          ThisWorkbook.ActiveSheet.Calculate
    End If
  End If
  s = ActiveCell.Address(, , , True)
  l = ActiveCell.Interior.ColorIndex
End Sub

      3. In the module with our code, add the following code to the very beginning before the current code.

      Dim cf As New Class1

Sub Auto_Open()
    Set cf.cX = Application.CommandBars
End Sub

Sub Auto_Close()
    Set cf.cX = Nothing
End Sub

      4. For the changes to take into effect, close the workbook and open it anew.

  10. Thanks for the code, your instructions are very clear and it works brilliant.
    However I did experience a problem when copying the code from the website I found it pasted without any line breaks or indents. I was using ie9 but when I changed to chrome everything worked ok. Thought I'd just say in case anyone has similar problems.

    1. Hi Paul,

      Thank you very much for letting us know!

  11. I can't seem to use these functions without getting a #NAME error.

    1. Hi Jim,

      Excel throws this error when it cannot find GetCellColor's functions. The most obvious reasons are:
      - The functions have not been added
      - They were added to a wrong location
      - The workbook was not saved as macro-enabled
      - There's some misprint in the function's names

      Will you, please, verify the items above?

      1. Thanks for the response Svetlana.

        My mistake was trying to call the Function from a hidden macro enabled workbook located in XLSTART while I was in a .xlsx workbook.

  12. Hi Svetlana,

    I am facing an issue with specific colors like dark green and dark red, the formula is mixing both of them, so if i have a dark green cell with value of 5 and another dark red with a value of 7 so in this case the formula is giving me 12 as a result for both colors , need your help please

    1. Hello Gary,

      Thank you very much for your workbook. You are absolutely right, this is Excel's bug. I've tried to optimized the code performance, which is why I used the .ColorIndex property. As it turned out it still has a bug – earlier Excel versions did not allow using more than 56 colors in one workbook. So, 56 is the maximum number that the function can return. In newer Excel versions beginning with Excel 2007, you can use millions of colors at a time. But the .ColorIndex function still returns up to 56 colors even for new colors.

      I've re-written the code using the .Color property that returns a full color code. Please copy / paste it anew, it should work well now.

      Thanks again for your worksheet, it gave me a clue!

      1. Thank you both Alex and Svetlana for your responses & assistance :)

    2. Hi Gary,

      It's hard to say what the cause of the problem is without seeing your colors. Can you please send us your worksheet (only colors, no data is actually needed) at support@ablebits.com? We will try to figure it out and fix the code if needed.

  13. Hi Svetlana,

    Thank you very much for this piece of code, it helped me a lot!
    However, I wonder if there is small bug inside or not
    (I don't know if my "pre" tags with SyntaxHighlighter below will work sorry for the inconvenience)

    The function "GetCellColor" returns ".Color" property :

    Function GetCellColor(xlRange As Range)
    GetCellColor = xlRange.Cells(1, 1).Interior.Color
    End Function

    but the other functions compare the values of ".ColorIndex" properties:

    indRefColor = cellRefColor.Cells(1, 1).Interior.ColorIndex
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.ColorIndex Then
    cntRes = cntRes + 1
    End If
    Next cellCurrent

    Shouldn't we always compare ".Color" properties ?

    Yohan

    1. Hi Yohan,
      This is a very good question! The code is correct. The point is that the .Color property returns the color RGB code, e.g. FFFF00 for yellow; while .ColorIndex returns the color index in Excel's color palette. That is why we use .Color when we need to know what color is used in a certain cell. When we are comparing two colors, we use .ColorIndex as a quicker way.

      1. Hello! Thank you very much for the code! I just added it to my worksheet and it works like a charm (I have also purchased the Suite). One question related to Yohan's: by adding the first VSB module, GetCellColor gives me the MS access color code. I take this is the same as 'Excel's color palette'. Correct?

        If so, question: How do I get the RGB code as output instead of the MS code? And where do I have to put the piece of code in the module above (sorry I am a newbie)

        Another question: how do I search for a single specific cell in a specific color across my worksheet? The input should be the MS code, while the output should be the location of the cell.

        Thank you!
        Vanina

      2. Hi Svetlana Cheusheva how are you i am new qamar
        i want to ask something

        i am doing job as computer operator

        i make result of 8 semesters each semester is come after 6 months BS.Engineering Degree is being given to students in 4 years
        Questions:
        1- I post summer result in some students record because they some subject
        i want when i post their summer result then changing effect shoud be on all semester result,
        2- but before knowing it is important that how can we merge more than one result combine or all results how will be in link
        If you understand my words then kindly give me reply and solve my problems I will be very thankful to you for this kind act

        Naveed

      3. Ok !
        in fact I needed to compare RGB codes, so now evreything is clear

        Thank you for your response

  14. Thank you for providing this code. It has worked well. My worksheet includes colours with a pattern (which represents pending) and I do not want to count these cells.
    Is it possible to add to the code so it does not count the colour if a pattern is also in that cell?
    Thank you
    Tammy

    1. Hi Tammy,

      In theory, this can probably be done. But to be able to say with certainty, we need to have a look at the colors you are using because very close colors may not be recognized by the script. So, if you can send us your workbook (without real data, only colors) and indicate which color(s) is a pattern, we will try to help.

  15. Hi Wanda,

    Sorry, I am not sure I can exactly follow you. If you can send us (at support@ablebits.com) your original workseet and the result you are trying to achive, we will try to help.

  16. I have an assignment that requires counting color blocks per wall (total), per column (total).

    Column South West 1 - Example: F,6 (DB-Dark Blue), G,6 (LG-Light Green), H,6 (A-Amber)
    Row 2
    Type B10

    The Whole point is calculating quantity of color glass per column/wall (4colums per wall).

    Thank you in advance.

    In kind,

    Wanda Rolon

  17. Thanks to you I am able to correctly count cells of a particular colour now in a column, but I really need to be able to count only the rows where both a colour is in a certain column and another has the words "Indoor" in it (i.e. 2 conditions to be met to count the row).
    I tried the formula: =COUNTIFS(Report.xls!$I:$I,$B$9, Report.xls!$AH:$AH,"(CellsByColor(Report.xls!$AH:$AH,A3))") which appears to work but always results in the answer zero. Can you see where I am going wrong please or suggest a better way?

    1. Hi Greg,

      There is no Excel formula that would let you count rows based on two different conditions at a time. This can be done by creating an additional column in your Report.xls sheet. If such an approach is acceptable for you, please let me know and we will try to work out a formula.

  18. Hi, by chance i get this article while surfing. excellent piece of information. How to count cells colour to denote a separate colour. For example, if there are five yellow,five red and 10 oranges in a row, say from a1 to a20. in A21, i want to stipulate a condition, where, there should be 5yellow (not less or more), similarly 5 red and 10 oranges, if these are satisfied, then I want a21 to be green colour otherwise, blank. How to effect it in a selected row and then i can copy the formula.May i expect your reply as solution.

    1. Hi Jraju,

      You can add this function to your workbook and then enter the following formula to A21:
      =AND(CountCellsByColor($A$1:$A$20,$B$1)=5, CountCellsByColor($A$1:$A$20,$B$2)=10, CountCellsByColor($A$1:$A$20,$B$3)=5)=TRUE

      Where $B$1, $B$2 and $B$3 are the cells with your color patterns (yellow, red and orange, respectively).

      If the cells with color patterns are on another sheet, then you need to add the sheet's name, e.g.
      =AND(CountCellsByColor($A$1:$A$20,ColorLookup!$A$1)=5, CountCellsByColor($A$1:$A$20,ColorLookup!$A$2)=10, CountCellsByColor($A$1:$A$20,ColorLookup!$A$3)=5)=TRUE

  19. Hello Jake,

    Regrettably, none of our Excel developers knows how to do this. The Excel Object model does not allow using the needed property in formulas, which is why it's impossible to create a conditional formatting rule to count the colored cells. Calling a macro manually as described in the article is the only possible way that we know.

  20. Hello,
    Thanks for the code. It is working but i have a question of how to set up a rule to use it. I used the Conditional formatting code.and now i am trying make a rule to count the number of green cells automatically for each column and return it to a specific cell. each column is a month and i want to know how many red green, yellow, and orange cells there were for each month. if you could shed some ligght that would be great.

  21. Thank you for adding the code in response to Connor's comment about the entire workbook. I am trying to get the sum, not the count for the workbook. I just want to make sure that code works for the sum as well as the count, and if so, what data range would I use? Thank you.

    1. Hi Josh,

      It's a very good question! It actually made me realize that the formulas we published in that section were not true.

      When using the function that counts and sums colored cells across all the sheets in a workbook, you need to use the following formulas to count and sum, respectively:
      =WbkCountCellsByColor(A1) and =WbkSumCellsByColor(A1)

      We've slightly modified the code and does work for the sum as well as for the count now. Just enter the second formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color (it's (A1) in the example above) and the formula will display the sum of all the cells shaded in this color in your workbook.

      Thanks again for your comment and your great question!

  22. Hi,

    Thanks for the code. It's very useful. I am looking for code that counts coloured cells and then recounts when a filter is applied to those cells. i.e. it counts all the coloured cells in the filtered data. Any help with this would be much appreciated.

    Matt

      1. Thanks for the response.

        Coloured manually - The data was originally conditionally formatted however I copied and pasted it to remove the conditional formatting so the data is effectively coloured manually.

        I am using Excels autofilter.

        Matt

        1. Matt,

          Try to find the following line in the code:
          If indRefColor = cellCurrent.Interior.ColorIndex Then

          And replace it with:
          If (indRefColor = cellCurrent.Interior.ColorIndex) AND (False = cellCurrent.Rows.Hidden) Then

          This line is used both in "Function CountCellsByColor" and "Function SumCellsByColor". You can change it in both functions if you want to count and sum the filtered cells, or in the CountCellsByColor function only.

          Does it work now as you want it?

          1. Thanks. I changed it and now it works perfectly.

            Find the following line
            If indRefColor = cellCurrent.Interior.Color Then

            And replace it with:
            If (indRefColor = cellCurrent.Interior.Color) AND (False = cellCurrent.Rows.Hidden) Then

  23. Hi Boyo,

    Regrettably, we do not know a way to make only one count for the merged cells. Our developers say this is a very tricky with a lot of pitfalls.

  24. Hi Svetlana,

    The code works amazingly and I thank you.
    Could you please assist me in with a code of how to count merged colored cells?

  25. Doesn't work. A window oppens giving the information of error in formula formatting. Any body could help?

    Thanks

    1. Hi Francisco,

      What Excel version do you use? Please note that the script works with Excel 2010 and 2013 only.

  26. Hello, thanks for the sumifcolor~I am manually adjusting the colors after I have entered the formulas, but now the formula will not automatically update. I have verified the "automatic" update is checked...verified other formulas are updating automatically. However, when I add another cell to the color I want to update my sum, it does not do so unless I click on the formula and hit enter.

    Please advise...and thanks!

    1. Hi Ian,

      This is how all macros and VBA scripts work in Excel, you do need to click on the formula and press Enter after changing the colors manually. Please see my comment 10 above for more details. Thanks for your comment!

      1. Thanks! Your site is going to make me look wicked smart. And I appreciate you.

  27. Hi Svetlana,
    Thanks for the code! It's just what I was looking for. For some reason, however, it's not counting correctly. I highlighted 42 rows: 27 black font, 15 red font and it's giving me a count of 16 when it's sent to count black font. I set up =CountCellsByFontColor(A3:A44,A1), where A3:A44 is the range and A1 is the color (black). Please let me know how to fix this. I would love to use this macro!

    1. Hi Svetlana, I deleted the coding and started again and it works now. Must have been my error. Thanks so much!

      1. Hi Cheryl,

        It's great that you've figured it out on your own! And thank you for letting me know.

  28. I believe I may have figured it out; it seems that the code will work for basic colors but will not work for custom colors. Thanks!

    1. John,

      I consulted Alex, our Excel specialist who actually wrote the code, and he says it should work with custom colors too, except for similar colors. BTW, which code have you tried - for cells colored manually or using conditional formatting?

  29. Hi John,

    Did you update a cell with the formula after changing the color manually (put the cursor in the cell, press F2 and Enter)? Please see comments 10 and 12 above for more details.

  30. Hi Svetlana,

    I've tried to use the code to do a count based on color; however, it doesn't seem to be counting correctly since the color I have on a table is red and I am trying to count whenever I manually update it to green (it currently is counting it regardless of what color I choose).

  31. Hi Mulugeta,

    I'm glad to know my article has been of help to you. I've posted all the code here and you can copy it to your worksheets. Thanks for your comment!

  32. Dear Svetlana

    Thank you for such interesting things.
    if you have more detail in addition to excel
    please send me the coding and the note.

    God Bless you

    Mulugeta

  33. Regrettably, it is not possible to make one count for the merged cells. More precisely, neither I nor our Excel developers know such a formula. If you happen to find a way, please do share it here : )

    1. could you add something like this to the code?

      Sub Count_Merged()
      Dim rng As Range, cell As Range, cell1 As Range
      Dim lngMERGE As Long
      Dim lngMERGEAREA As Long
      Dim ws As Worksheet
      Dim wb As Workbook
      Dim vari As Variant

      Set wb = ThisWorkbook
      Set ws = wb.Sheets("Sheet2")
      lngMERGE = 0
      ws.Select
      With ws
      Set rng = Range(.Cells(1, 1), .Cells(100, 9))

      For Each cell In rng
      If cell.MergeCells = True Then
      lngMERGE = lngMERGE + 1
      lngMERGEAREA = lngMERGEAREA + cell.MergeArea.Count
      Set cell1 = cell
      'Debug.Print cell1.Address
      End If
      Next cell
      End With

      vari = lngMERGEAREA / lngMERGE
      vari = lngMERGE / vari

      MsgBox "Number of cells merged and counted as one instance is: " & vari

      End Sub

      that counts the cells where cell.merge is true and then adds the cell.mergearea.count values. then it divides the cell.mergearea.count by the cell.merge is true count AND finally divides the cell.merge is true value by the previous value.

      lol

  34. I have a problem regarding on counting of cells by color, if I merged two cells the total count will also two. Is their any additional formula to make only one count for the merged cell.. Thanks. !!!

  35. Hi lkara,

    In which Excel version did you run the script? We tested it in Excel 2013 and 2010 only and it may not work correctly in earlier versions. In you are using either 2013 or 2010, please specify your version of Windows and the localization of both Windows and Excel. Thank you!

  36. I tried the conditional formatting VB code and get a syntax error pointing to the first line - Dim indRefColor As Long. Any suggestions ?

  37. Hi Abdo,

    We have not been able to reproduce this error. Can you please send us your workbook for testing (or any other sample workbook that reproduces the issue) and we will try to figure it out? If it is possible, please drop me a line using the contact form on my personal page.

    Thank you!

    1. Hello,

      Had the same problem like Abdel - any luck with the debug?

  38. Thank you for your tutorial.

    I tried to use "How to count by color and sum cells colored using conditional formatting" but it did not work. When debugging there was an error at the line:

    indRefColor = ActiveCell.DisplayFormat.Interior.ColorIndex

    as the debug mentioned that indRefColor = 0

    What's the error in my implementation ???

    Thank you
    Abdo

    1. Hi

      I'm trying to sum all cells with a certain background colour across a workbook. I have followed the steps above however when I enter the formula and press enter it takes me to the page with the code and says Compile error: Sub or Function not defined. Is there a tweak I need to make to the formula?

      Thanks

      1. I see Faith said: "I'm trying to sum all cells with a certain background colour across a workbook. I have followed the steps above however when I enter the formula and press enter it takes me to the page with the code and says Compile error: Sub or Function not defined. Is there a tweak I need to make to the formula?" on 12/19/2016.

        Was there every any resolution to this comment as I am experiencing the same issue. Thanks!

  40. Hey!

    The VBA for counting colored cells worked wonderfully, but I need to count coloured cells across an entire workbook not just one worksheet to have an overall count. Do you know of any way I can do this?

    Thanks!

  41. Actually.... my cursor had to be in the cell with the formula for the F2 to work. I should have included that in my previous response for others to reference. Thanks!

  42. Thank you so much, Svetlana. That worked perfectly. This function is going to be so helpful.

  43. Hi Gwen,

    This happens because of the following specificity of Microsoft Excel. Any macros, VBA scripts etc. work on the worksheet's level and functions are called with a change of a worksheet's data only. Changing the font color or cell color is not perceived as the data change by Excel. So, after coloring additional cells, simply place the cursor to any cell, hit F2 and Enter, the sum of the colored cells will get recalculated.

    Hope this helps a thanks for your comment!

    1. i am working in a company as a MIS Executive but i have some problem in vba coding can you help or can you give the some traning session of vba coding me

      1. Sorry, we do not provide training services. But I am sure there are plenty such courses on the web.

  44. THANK YOU, you saved my ass at work! ;)

  45. Hi, Svetlana -

    I used your script and formula for a spreadsheet at work - to sum cells of certain colors for a reconciliation. It was fantastic. However, when I color additional cells for values that my reconciliation found had been missed, the related formulas do not update to reflect the correct sum. Why is that? We are working in Excel 2013.

    Thanks.

    1. Yes I can send it to you. It is a pretty large sheet that does many vlookups to gather the data. All data is within the spreadsheet. The tab (worksheet) I am working on is called "SD SLA (ALL)". I am trying to count all the failed (RED) cells and the non-failed (no color) cells. I am using Excel 2010. Is there an email address where I could send you the file directly?

      Thank you
      Ray

  47. Hi Sam,

    From your description, your task looks similar to the example described on step 5 of How to count by color. In my example I used the CountCellsByColor formula three times to count red, green and yellow cells. For example, I used =CountCellsByColor(F2:F14,A17) to count all red cells in the table. You enter this formula in some empty cell in your table where you want to output the sum, say, of all red cells and replace F2:F14 with your range of colored cells and instead of A17 enter an address of any cell colored in red. Copy the same formula in as many empty cells as many different background colors you want to count, just type a different cell address after the comma, i.e. the address of any cells with a certain background color, yellow, blue or other.

    If you want to achieve something different, please let me know or even better post a screenshot so that we can exactly understand your task.

  48. Hi, thanks for useful tutorial. I have excel data which cells contain number 1-1500 and each number colored with various background colors. I want to sum how many cells are red,yellow or blue. I've got visual basic editor and SumCellsByColor function but I get confused how to sum by color using that function in this case because likely different with your tutorial, is it? Can you help? :)

  49. Hello Svetlana,

    I really like your VB code for "How to count by color and sum by color in Excel". I just need to take it one step further. I'm trying to use the "How to count by color and sum by color in Excel" to count the number of Red (Misses Service Levels) and Green (Achieved Service Levels) by row on a Summary tab. The Summary tab is populated by 12 Monthly tabs (Jan-13, Feb-13, Mar-13 – Dec-13) via a VLOOKUP command. Once on the Summary tab, if the Service Level was Missed, a conditional format changes the fill color to RED, if the SLA is Achieved, the fill color is GREEN. Even though the Conditional Formatting shows the cell as RED or GREEN, your VB script still believes them to be NO COLOR Fill. Any thoughts?

  50. Hello BreBre,

    I'm a bit confused. Do you mean there's a misprint somewhere in the article or it's already time to move forward and think about Excel 2020? : )

    1. What you have been demonstrating here is Excel 2020. Awesome piece of code !!!

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