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 →

Table of contents

Comments page 3. Total comments: 829

  1. I tried using your Subcountbyconditionalformat, but I do not get the correct count. I created a calendar view that highlights Saturday & Sunday as gray based on the date in the cell. I also have a conditional format that states if the date is not within a certain month to leave the cell white. The counter seems to count any cell that has a conditional format that could make it gray instead of the end result of multiple conditional formats. Is this how it should operate?

    1. Honestly, this tool is amazing, not even chat GPT suggested a precise process as the one stated here.

      Thank you

    2. Hi! The function counts only cells with a certain color. Check your data and formulas. It is possible that the color is incorrectly specified in the formula.

  2. Hi all,

    I just added this sum function "=SumCellsByColor" in my workbook earlier and save it. it worked perfectly but when I open the same excel again after few days, how come it is not automatically calculating. I have no idea have to resolve the issue. Any help please on how I fix the problem? Thanks!

  3. The macro for counting the conditionally formatted cell works great - but the popup is NOT what i need - i want the count to post to a Cell. is that possible without having to do it manually myself? in other words - if 6x in a my row/range - the "condition is met" (green) - i want 6 to appear in a summary table cell below. Then i want to do that again for a different range/row (different team member) and so on. Thoughts?

    1. Hi! This is possible by changing the macro code and specifying the cells to which the results will be written.

      1. Hello! This is very helpful, thank you! I also have a question about editing the macro code. I am not a coder, can you please explain where (and how) in the conditional formatting macro code we would need to edit to output the count into a cell and not a popup box? Thank you!

  4. IT is working perfectly ...Thanku for this

  5. How do I adjust either the formula or the module to allow me to count cells by color in multiple ranges? Is it possible to add multiple ranges in the current formula in some way or do you have to adjust it somehow? For instance;

    =CountCellsByColor((B5:B12,E5:E12),AM5)
    Where B5:B12 and E5:E12 are the two ranges I want to count cell in by color to get the sum of cells with the color (ignoring any occurance of the color in columns C-D)
    And AM5 is the cell containing the color I want to count in the two ranges.

    1. Hi! I can recommend you create a separate formula for each range of data. Then summarize these results.

      =CountCellsByColor(B5:B12,AM5) + CountCellsByColor(E5:E12,AM5)

  6. Hi, great work, thank you.
    I am having some issues, I have 29 cells, of which 8 are green fill, through conditional formatting condition ">0".
    I have another cell, manually filled with green, as sample colour, as specified in tutorial. When I run the macro, it counts "29" with colour "000000".
    Some help please'?

    1. Hi! I can't guess what macro you are using. To count conditionally formatted cells, use a special macro, which is in the article above. Also note that the color code in the sample and in other cells must be the same. To get the color code, there is also a special macro.

      1. Hi, I am using "How to count and sum conditionally formatted cells using VBA macro". I can't use get color code, because in that function: "Note. The functions only work for colors applied manually, and not with conditional formatting."

        I am highlighting my 29 cells using condition formatting, condition is numbers above 0, and I use Custom Format and select Green colour.

        Then I run the macro, using Sample Cell with same Green colour, but the macro return is: 29 (even if cells are Green and Red color mixed) and color "000000"

        Thanks!

        1. Hi! If you read this article carefully, you'll see a paragraph: Count and sum conditionally formatted cells.

          1. Hi, I did read carefully. I don't understand? The cells were conditionally formatted? The condition is, if > 0 then green fill...

            1. Hi! If the SumCountByConditionalFormat macro does not work, then the color of the conditional formatting and Sample Cell are different. Try copying the color of the conditional formatting to the Sample Cell using Format Painter. Use the sample file linked at the end of this article.

  7. This saved my life (or at least a lot of time) this afternoon! Thanks so much!

  8. Hi,

    Can I do any of these within a date range?

  9. Hello, this is excellent and extremely handy. Is there an easy way to modify the code so that it functions similarly to COUNTA? I have some cells that are colored but are blank other than that and it would be nice if the function would only count those with data in it. Thanks!

    1. Hi! Your VBA code change request goes beyond the advice we provide on this blog, sorry.
      To count colored cells that have values in them, try this formula:

      =SUM((DEC2HEX(GetCellColor(A10:A20))<>"FFFFFF")*(A10:A20<>""))

      I hope it’ll be helpful.

      1. I’ll try this, thanks!

  10. I'm trying to count cells by color. I've pasted the code and I can see that it calculated the Formula result perfectly in the Function Arguments window. However, when I select OK, the cell returns #VALUE. I have a feeling it's because the range selected has cells that contain "". I used an IF function in the cells to return the value as "" to avoid having 0's fill up my table. Any suggestions?

    1. Hi! The information you provided is not enough to understand your case and give you any advice. What formula are you using? Give an example of your data.

  11. This is a great solution.. Many Thanks for this functions..

  12. These functions are exactly what I was looking for, however all I get is the dreaded #NAME? error.

  13. Pls, how do I sum a cell with color when it is equal to a particular date?

    Which means; Sumifs using the cell color and date as the criteria.

  14. Svetlana,

    This is a wonderful function. Thank you!

  15. if I have the same cell layout on several tabs within the same workbook, how could I count the number of colored cells in the work book on Cell A6 for example?

    1 workbook, 100 sheets.

    Count how many each of the cells are colored in the same cell.

    sheet1 A5
    Sheet2 A5
    sheet 3 A5
    etc

    have 2 colored cells.

    Sheet1 C50
    Sheet2 C50
    Sheet3 C50
    etc
    have 50 colored cells.

  16. Hi,

    The SumCellsByColor worked great in a local instance of Excel on my desktop, but when I move the script to Microsoft365 Excel, I'm getting 125 errors. I'm honestly not sure where to begin.

    Examples of errors:
    Line #2: ';' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    Line #2: ',' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    Line #2: ',' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    Line #2: ',' expected.
    Function SumCellsByColor(data_range As Range, cell_color As Range

    1. Hi! Check which characters you are using as delimiters. Download the sample file linked at the end of this article. You can use this file or copy the VBA code from it.

  17. Hi, and thanks for providing the code. For some reason my Excel does not recognize the funiction. When I type =CountCellsByColor(C4:E5, G4) Excel returns a popwindow telling: "There is a problem. Do you try to put in a function? When the you start with = Excel thinks it is a function". So I assume I have whatever wrong setting making my Excel unable to recognize the function. Can you help me?? Thanks in advance.

  18. Hi,
    I am trying to count 6 different cell colors into 1 number (red, yellow, pink, blue, green,purple, orange=7 ). Can I assign multiple values to get the desired results? Also is there a way that each counted cell is counted as .5? I'm trying to track supervision on each staff by 6 different supervisors and each colored cell represents 1/2 hour.

    1. Hello! You can use a separate CountCellsByColor function for each color and then sum the 7 results. If necessary, you can multiply the result of the CountCellsByColor function by 0.5

      1. Thank you!

  19. Work well for me.
    =SumCellsByFontColor

    tks so much

  20. I need help with a formula that I'm needing for a new work spreadsheet that I'm trying to create to keep track of which employees have completed their yearly certifications. I have employee names in cells the first 25 cells of columns B through W (they are broken down in a specifc way so I can't add or combine more in a column). As each employee Right now I am using a CountA formula that I'm using showing the total of employees (= counta (B2:W25) but as I highlight the employee names when I get their confirmations I want that cell subtracted from the total so I can see at a glance how many employees are left. I read about the count by cell color formula and that would work but I can't figure out how to combine the two to get a total. In my mine I thought I could put =counta(b2:w25)-CountCellsByColor(b2:w25) and that would give me my total but it doesn't. Please help.

    Thanks in advance for any and all help!

    P.S. I have Ablebits downloaded on both my personal and work computers and it's helped me so much. Glad I downloaded and bought it.

    1. Hello! In the CountCellsByColor function, specify the second argument – Cell_color. This is a reference to the cell with the target fill color.
      For example,

      =COUNTA(B2:W25)-CountCellsByColor(B2:W25,A1)

      Hope this is what you need.

  21. Countcellsbycolor worked perfectly thanks very much. Is there a way to modify the code so it counts the number of rows of a colour within a certain range? I.e. all rows from A10:A50 that are coloured the same as B2.

    1. Hi! The first argument of the CountCellsByColor custom function is the range in which you want to count cells.

  22. How to get the totals updated when a color is changed?

  23. CountCellsByColor Function Not Working, When I use this function then result are 0, please solve this problem..

      1. I want to count individually cells. For exp- i have hugh excel data and i want to count how many green cell & how many pink cell in table, but the proble are when i used your function then result are 0 every time .
        That's means - green cells -- how many
        pink cells -- how many
        pleace help how to do.

        1. Unfortunately, I can't work with your data. Try to use the sample file linked at the end of the article. Use the CountCellsByColor function, don't forget to specify the cell with the desired color.
          To count conditionally formatted cells, use VBA macro, as described above.

  24. I modifed the code to count the celss with conditional formating as below but it is giving value error
    Function CountCellsByColor(data_range As Range, cell_color As Range) As Long
    Dim indRefColor As Long
    Dim indCurCell As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim cntCells As Long

    Application.Volatile
    cntRes = 0
    cntCells = data_range.CountLarge
    indRefColor = cell_color.Cells(1, 1).DisplayFormat.Interior.Color
    For indCurCell = 1 To (cntCells)
    If indRefColor = data_range(indCurCell).DisplayFormat.Interior.Color Then
    cntRes = cntRes + 1
    End If
    Next
    CountCellsByColor = cntRes
    End Function

  25. Counting of colored cells does not work on cell ranges that are conditionally formatted.

    1. Hi! Pay attention to the following paragraph of the article above: Count and sum conditionally formatted cells.

  26. When I change a cell's color, will the totals be updated instantly?

  27. This is very helpful!

    Is there a way to combine CountCellsByColor and CountCellsByFontColor? I have a set of data that has 5 different colors and 2 different font colors and trying to get 10 unique values from it. I was thinking it would incorporate CountIfs but my tests haven't been working.

    Thanks in advance!

    1. Hi! Unfortunately, it is not possible to combine the two custom functions. A new custom function must be created.

  28. Hi, great extention and very helpful, thanks. But if I need to pick just several cells instead of a certain range I can't understand how to.
    for example instead of
    =SumCellsByColor(D18:F21;O20)
    I need to summm only four cells, like
    =SumCellsByColor(E18+F19+E20+D19;O20)
    but it doesn't work. What am I doing wrong?

    1. Hi! Use either a single cell or a range of cells in the SumCellsByColor function. For example,

      SumCellsByColor(E18)+SumCellsByColor(F19)

  29. This is great stuff! Thank you. Got a question, though. Two columns side-by-side. Each column has cells that are colored either red or green. Trying to count the cells where it's red in column 1 and green in column 2, or green in column 1 and red in column 2. Also need to count the ones that are red in both columns and green in both columns. Is there a way to do this?

    1. Hi!
      This formula can count how many cells in the both columns have a value equal to cell A2 and are colored red.

      =MIN(SUM((GetCellColor($A$2:$A$20)=255)*($A$2:$A$20=A2)), SUM((GetCellColor($B$2:$B$20)=255)*($B$2:$B$20=A2)))

      I'm not exactly imagining your data, so try the formula below as well. It compares the cells in each row in pairs.

      =SUM((GetCellColor($A$2:$A$20)=255) * (A2:A20=B2:B20) * (GetCellColor($B$2:$B$20)=255))

      Use the custom functions from the article above.

  30. The Count and sum by color across entire workbook was well explained and simple to embed into my Workbook.
    Thankyou so much!!

  31. Hello , I am using MICROSOFT OFFICE LTSC PROFESSIONAL PLUS 2021 and I follow the same formula and it still not work #NAME,
    Then I download the excel sample and copied the entire data with the formula and the result is the same #NAME

    Any help?

    1. Hi Fernando,

      From the sample workbook, you need to copy not only the data/formulas but also the function's VBA code. Have you done that?

    2. Hello!
      Have you saved the file where you added the code as a Macro-Enabled Workbook (.xlsm)? If the issue remains unresolved, please try downloading our sample workbook provided at the end of this article, unblocking the macro (Microsoft automatically blocks macros in any files downloaded from the internet), and see if the function works in that particular workbook.

  32. I have the following problem with respect to "Count on conditionally formatted cells"
    1. For my data I need to filter it first on 1st column lets say with INDEX or FILTER functions
    2. Range received as output of Step 1 needs to be used as Input of "Count on conditionally formatted cells"

    How to do this ?

    Example : lets take the same sample table as you showed but add another column before the 1st one as category
    Say choose 2 categories as "Fruit1" and "Fruit2" and place these category against each of 2nd column data

    Now I need separate "Count on conditionally formatted cells" for "Fruit1" category and separate for "Fruit2" category ...

    Pls help on how to achieve this ...

      1. hmm... I am not familiar with VBA.. Is someone could help with this ?

  33. how to add the countecellby color in excel and not run the VBA.
    thanks !

  34. Hello! First of all, I really appreciate all the tips and tools; they are extremely helpful. So here is my problem. I added the Macro for CountCellsByColor and saved. Wrote for formula in the associated cell reference and it calculated, but the number it calculates is not correct. for example, I am approved for 10 staff, I have 9 currently and one potential new hire. So, instead of it count 9, it is calculating 14, what I am doing wrong?

  35. Using CountCellsByColor and SumByColor and they work GREAT! Thank you!!
    How would I then get the MAX from the SumByColor?
    I have tried a few things and none work, so far. Unfortunately, I do not code very well...

    • Looking for a MaxByColor routine.
    Any help would be most appreciated!

  36. Hey all! Thanks so much for posting this code. Is it possible to output conditionally formatted color count to a cell so that I can sum cells? I was able to use the macro to do it perfectly, but I need to be able to separate counts and then sum for a total. Is there any way to do this?

    1. Nevermind, I found the answer below from Alexander and have used this successfully. Thank you Ablebits team for this help!!!

  37. I can't get this to work to save my life. I'm trying to get it to count cells by color, used your formula and it if throws #NAME? in the place of "CountCellsByColor".

    1. Hi Steven,

      Did you save the file where you've added the code as a Macro-Enabled Workbook (.xlsm)?

      If the error persists, please try to download our sample workbook at the end of this article, unblock the macro (by default Microsoft blocks macros in all files downloaded from the internet), and see if the function works in that workbook.

      1. Same problem. What is need to be followed in the 1st paragraph?

          1. done this but still getting the #name? error

            1. Hi!
              #NAME? error means that either you got the function name wrong, or the function is not present in your version of Excel.

              1. Having the same issue. Copied and pasted the CountCellsByColor VBA code. Saved workbook as .xlsm. Still getting the "#NAME?" error when trying to use the function.

  38. Much appreciated. I'm finding the coding her very helpful.

  39. Hello All,

    it was worked if count by number but not if it count by date. could you please help me on this.

    appreciated for your support.

  40. Hello. Thanks a lot for the codes. I am wondering whether it could be possible to use the function for conditionally formatted cells in a cell as a general function to be inserted in whatever cell (not as a macro in a dialog box). I.e. I am looking for adjusting the function for summing colored cells which would work for conditionally formatted cells as well. Any ideas about adjusting the code?
    Thanks.
    Stanley S

      1. Hi Alexander! Below, in January Svetlana Cheusheva wrote a reply that there is only a macro available for both sum&count. But directly under this comment I am writing now I already asked you about some possible way of adjusting your code for function from "count" to "sum". Is it possible?
        Thanks!
        Have a nice day,
        Stanley

        1. Hello!
          Try this code:

          Public Function SumByColorCells(CellRange As Range, TargetCell As Range)

          Dim TargetColor As Long, SumCells As Long, C As Range

          TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
          For Each C In CellRange
          If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then SumCells = C + SumCells
          Next C
          SumByColorCells = SumCells
          End Function

  41. SIR,

    I AM NOT GET COUNT OF COLORS WHERE CONDITIONAL FORMATTING APPLIED BY ABOVE MENTIONED SOLUTIONS/

    KIDNLY HELP

    1. Hello!
      This custom function counts all cells shaded by a certain color. Including with conditional formatting.

      Public Function CountByColorCells(CellRange As Range, TargetCell As Range)
      Dim TargetColor As Long, Count As Long, C As Range
      TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
      For Each C In CellRange
      If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then Count = Count + 1
      Next C
      CountByColorCells = Count
      End Function

      Function CFColour(Cl As Range) As Double
      CFColour = Cl.DisplayFormat.Interior.Color
      End Function

      1. Hi Alexander!
        Thank you for solving the case. Do you have any ideas on summing the range of cells including conditional formatted cells? Replacing "count" with "sum" in your code simply does not help but I do not know why...
        Thank you!
        Stanley S.

  42. I am using CountCellsByColor. Can I count cells that also contains a certain text? It counts the cell colors fine, but other users are highlighting other things in the same colors which are now giving wrong calculations. eg cells that are yellow, but also contain 'test' for example.

  43. This was really useful, I was trying to do an analysis and the way the analysis was done was by color. But I wanted to count some colors and wanted to do the "if" statement, is there a way I can do that?

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  44. Nice code.

    Appears to be a small typo

    VBA macro to count and some conditionally formatted cells.
    Sub SumCountByConditionalFormat()

    ... and SUM conditionally ...

    1. Hi Ron,

      Oops, a silly typo indeed. Fixed, thank you for pointing that out!

  45. Your code for "How to sum by color in Excel" is excellent with only one issue, it doesn't automatically recalculate if you color the cells after setting the the calcuation cell. Forcing the recalculate works well and my sheet is set to automatic recalculation but it will not. I assume closing and re-opening will also cause the recalc.

    Any help appreciated.

  46. Hello,

    I was able to use SumCountByConditionalFormat just fine as a sub however i was wondering if there is a way to make it a function? so instead of preselecting and answering a msgbox i could place the information in the formula bar?

    Thank you,

    Halle

  47. I really like the function. I am wondering If there is a way to make it refresh on color change. in other words, I use it to track tasks. So when I complete the task change the color of the cell. If I use the paintbrush it works but if I just apply the new color it does not update until I click into the cell and click out.

  48. After add this module in excel, it need to loading everytime when I edit the excel, may I know how to solve this problem?

    2. It also is no responded and blur screen loading. Is the function not suitable for my excel or did I set sth wrong?

  49. I added in your VBA for Count by color across entire workbook and saved it as a macro enabled excel file, i created a new tab and filled a cell with the colour i wanted to contain cell A1 i then used the formula =WbkCountByColor(A1) in the adjacent cell. the following error was then generated: Microsoft visual basic Compile error: Sub or function not defined. Seems to relate to text in the code CountCellsbyColor? not sure how to remedy the situation?

    Thanks

    1. Hi!
      For this function to work, the code for the CountCellsByColor and SumCellsByColor functions must also be added to Excel. Read the Note carefully after the function code.

      1. Thanks, I got it to work and finished the workbook, however after closing and reopening the file in order to get the function to work it now reads VBAProject.Module2.CountCellsByColor(b2:b8, b2)
        Any idea why this has changed as now the formulas don't work and I'll manually have to edit them all to get the functionality back?

  50. Is there a function for the macro "Count and sum conditionally formatted cells" ?

    I need the number provided as an output in a cell that I can leverage with other formulas elsewhere.

    1. Hi Eric,

      Nope. For conditionally formatted cells, we only have a macro.

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