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 2. Total comments: 829

  1. Is it possible in Excel for Web (M365) to count cells by background colour within a specific date range? I am told that I cannot access VBA online....

    for example I want on a second sheet to have a reference to how may fields in A:A on the first sheet are Green and fall between Jan 1st and Jan 31st. I then want to repeat this operation for Feb, March etc.

    So would look like:
    Month Green NotGreen
    Jan 12 20
    Feb 20 10
    Mar 10 5

  2. Thank you. This is the best I've seen online so far.

    I have a waterfall which is conditionally formatted. The msg box VBA produced the right aggregation for one row. However, I have a waterfall and would like to return aggregated values for each row based on the conditionally formatted color? [in this case grey...(the VBA msg box color result was D9D9D9)]

    How can I get the sum results from the msg box, but as an output from a function? For example, in the data set below summing from left to right, the starting points would be 9990, 2003, 840, 569. I have over a 100 rows currently which expands monthly. The aggregations for each row is precedent for other calculations within that row. I hope you can help. (fingers crossed)

    11/1/2023 6,693 9,181 2,672 831 569 345 249 171 134 94 66 53 43 32 21 21 20 10 8 8 3 10 6 (3) 2 (5) 0 0 0 0 0 0
    12/1/2023 6,228 8,444 1,713 840 520 316 228 156 123 86 60 49 39 29 20 19 18 9 7 7 3 9 5 (2) 2 (5) 0 0 0 0 0 0
    1/1/2024 5,142 8,686 2,003 821 509 309 222 153 120 84 59 48 38 28 19 19 18 9 7 7 3 9 5 (2) 2 (5) 0 0 0 0 0 0
    2/1/2024 5,000 9,990 2,157 884 548 332 240 165 129 91 63 52 41 31 21 20 19 10 8 8 3 10 6 (3) 2 (5) 0 0 0 0 0 0

    1. Hi! Unfortunately, there is no solution how to get the sum of conditionally formatted cells using a custom function. Use a macro.

  3. Is there a way of modifying the CountCellsByColor function to treat merged cells as just one?

  4. This doesnt work when counting colour on conditionally formatted cells - it only works on cells which have been coloured manually. How do you fix this?

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

  5. The Count and Sum routines are excellent and work very well for me.
    How about a MaxCellByColor routine?
    In one (1) of 3 tabs, I track my bicycling Time (Col A) and then Date (Col B), year by year.
    The condition that exists is I use my bike outdoors and also backed up to a trainer for indoor riding - This data is Cell Colored different from outdoor rides.
    I need to be able to breakout the MaxByCellColor for the subset of data for the trainer rides to isolate the Longest Time single time (MAX) spent on the Trainer.
    All other stats I can pull using the Count and Sum routines.
    I tried a few things, but I am a very early beginner in coding... ;-)
    Any suggestions - Thanks!

  6. Hi, I tried to paste the custom function “CountCellsByColor” in my view code area of my worksheet and it’s coming back with “#Name?” as the answer in the cell I want to count all my green cells. Can anyone assist?

  7. Hi! How do I count only individual characters of specific font (colour) in a the range of cells?
    For example, I have a range of cells containing simple text strings: [B V A] [B I M] [M K B] . The first "B" is black (auto) coloured, the second and third "B"s are red coloured.
    How do I count separately black B's and red B's (to get the result: black B - 1, red B - 2) in the range?
    Thanks!

  8. Thank You!
    Question - I am trying to nest CountCellsByColor.
    =CountCellsByColor(B2:Q33, B4) and it produced 37 (the number I was looking for).
    Now I am trying to count the colored cells if they are blank.
    =COUNTIFS(B2:Q33, "", B2:Q33, CountCellsByColor(B2:Q33, B4)) and it keeps returning zero which is not correct. What am I doing wrong?

    1. Hi! The COUNTIFS function cannot use other functions and formulas as criteria. To count empty cells that are highlighted in a specific color, use the SUMPRODUCT function.
      To get the code for the desired color, use the custom function GetCellColor() as described in the article above. Identify empty cells using the ISBLANK function.
      The formula might look like this:

      =SUMPRODUCT((GetCellColor(A1:A20)=GetCellColor(H1)) * ISBLANK(A1:A20))

  9. Hi Alexander,

    How to count cells by color in Excel;
    This works perfectly and has saved many tiring works.

    The counts are correct but when I change the colors of the cells, the formula doesnt pick this up, unless I go back to cell and press "Enter" again.
    Am I missing something?

    1. Hi! Only when you change values in the worksheet does Excel automatically recalculate formulas. The result is not changed by changing the cell format. Therefore, changing colors does not automatically recalculate.

  10. Hi. I love Ablebits! But, I have a problem. When trying to use Count and Sum by color, the Insert result as formula checkbox is not available to me. It works and I can paste the amount in my spreadsheet but not the formula. I am assuming there is some setup or option I have missed, any suggestions?

  11. Hi There,

    How to count conditionally formatted cell with RED Color?

    Count how many RED Color cells...

    1. Hi! Carefully read the paragraph in the article above: Count and sum conditionally formatted cells. Ensure that you understand the information presented.

  12. CountCellsByColor works fine using the Excel application (on Windows 10) but when shared through a link and viewed from a browser I get the #NAME?.
    After removing the line containing "Application.Volatile" it also works fine from a browser view.

      1. Thanks! understood. Nevertheless, I'm sharing in viewing mode only so no changes can be made and all Excel Online results look correct after removing the Application.Volatile line. Looks like the Excel Desktop calculated results have been saved and are shown in Excel Online without needing to run VBA(?)

        1. Hi! Application.Volatile causes Excel to recalculate the values of the custom function every time there is a change in the worksheet. Since it doesn't work in Excel Online, it causes an immediate error.

  13. Is there a way to use your "How to count and sum conditionally formatted cells using VBA macro" script listed above in a cell as a result, instead of a pop-up box as you have shown in your sample? I need to be able to total several columns that are conditionally formatted (and regularly updated with new data) with a yellow highlight.

    1. Hi! To count the sum of cells highlighted in a particular color, you can use the custom GetCellColor function. For example:

      =SUM((GetCellColor(A1:A20)=65535)*A1:A20)

      This function is described in detail in the article above.

  14. Hi, the count cells by color funtions work great! But is it possible to combine these and count cells with specific fill and font colours, e.g. yellow cells with red font?

    1. Hi! To count yellow cells with red font, try using a formula like this:

      =SUM((GetCellColor(A1:A10)=65535)*(GetFontColor(A1:A10)=255))

      1. Thanks! That formula only seems to return a 1 or a 0. I've been able to achieve the desired result by using GetCellColor and GetFontColor in two new colums, then using COUNTIFS to count the required cominations. I'm thinking there must be a better way though.

  15. Hi :)

    Is it possible to make this function continue to work once the data is sorted? Everything worked perfectly until I tried to sort my data, then it changed my coloured cell to #REF (see example. =CountCellsByColor(H6:ON6,#REF!) ) and now I just get #VALUE!. I made sure I picked a cell that would never move positions but still the formula couldn't handle the sort.

    1. Hi! I was unable to reproduce your problem when sorting the data. This error usually occurs not when sorting, but when deleting rows referenced in the formula.

  16. Hey, the code countscellsbycolor works fine but if I change a cell color the value of my code doesn't change. I have to clic on the code for the value to change.
    It is easier if the value change instantly so i don't have to clic on the code each time.

    1. Hey, Excel automatically recalculates formulas when a cell value changes. A color change is not considered a value change.

  17. Did exactly as instructions suggest, doesn't seem to work.
    Used this formula: =CountCellsByColor(I61:I66,I55)
    Had I55 cell in color
    Returned response is "#NAME?"
    Using Mac Excel
    Thank you!

      1. Hi, this seems to be working for me (thank you!) however I need it to calculate two separate columns that are not directly next to each other. For instance, E8:E50 and G8:50. When entering =CountCellsByColor(E8:E50,G8:G50,A52) it returns #VALUE! When I do =CountCellsByColor(E8:E50:G8:G50,A52) it counts the cells in column F but that's the only way the formula will accept it. What am I doing wrong?

        1. Hi! The formula returns a #VALUE! error because you have written the formula incorrectly. Read the manual above carefully. You cannot use two data ranges for counting.

          1. Thank you. As a novice, I guess my question is, is there at all a way to count more than two columns (that are not directly next to one another)? Whether in the formula or within the code of the UDF? Thank you.

  18. Hi, I have a task to count a quantity of red numbers among numbers which in black in excel cell. Is there any formula which can help me.
    For instance in a cell I have 4,5,7,9,3 and 6. Numbers 4 and 6 are in red, whereas others are in black as usual. So in the cell I have 2 quantity of red numbers. How can I use excel formula (if present) in order to save time.

    1. Hi! All the necessary information is in the the following paragraph of the article above: Sum values by font color.
      If all your numbers are written in one cell as text, it is not possible to count the numbers by color.

  19. Hi there,

    How to count the conditionally formatted cell with color RED?

    All the above method seems not working

    Pls guide...

    Thanks

    1. Hi! I can't write the formulas for you in your workbook. Follow the detailed instructions in the article above. You can use the sample file linked at the end of the article.

  20. I am using the SumCellsByColour whic is fantatic - thank you!

    Could anyone help advise how I could also add an additional if condition. Basically I want to add a coloured cell as long as another cell in my table also has specific text. e.g. sum the coloured cell as long as cell C2 = "include". Grateful for any advice or guidance that anyone can offer.

    1. Hello! Use the GetCellColor function to get the color of the cell. I believe the following formula will help you solve your task:

      =SUM((GetCellColor(D2:D20)=GetCellColor($K$1))*(J2:J20="text"))

      K1 - cell with color sample.

    2. Should have said here where I put C2, this would of course be a range in a table corresponding to the range where I'm looking at colour. So SumCellsByColour(table_col_1,colour) as long as table_col_2 = "specific text". Hope this is clearer.

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

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

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

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

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

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

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

  28. Hi,

    Can I do any of these within a date range?

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

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

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

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

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

  34. Svetlana,

    This is a wonderful function. Thank you!

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

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

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

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

  39. Work well for me.
    =SumCellsByFontColor

    tks so much

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

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

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

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

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

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

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

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

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

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

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

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