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 9. Total comments: 833

  1. Love You, it's exactly what I wanted.

  2. This is awesome. Thanks!

  3. Is there a way I can sum amounts by color, but the colors are in one column and the amounts (without color) are in another column?

  4. Hi, Really It's very useful and color Counts, Sum by Color tool, yes, it can count cells by font or background-color Well explained. thanks a lot...

  5. Hi - this is super useful! However is there a formula I could use to sumif the cell colour matches and the font colour matches?

  6. I just want to say thank you for sharing you knowledge. It is very useful.

  7. i cant edit the file to include more colours?
    I am trying to create a file that has all world countries and depending on the maual coloring, it will tell you pending countries you dont have yet. I need 7 colours, with one variant of each (light red, dark red, light orange, dark orange etc)
    plz help.

  8. Thanks for the coding - cut my work effort in half! Is there any way that this can be implimented into a COUNTIF/COUNTIFS formula - Ive tried majority of things and im not getting any results.
    The plan is that I would like to find a number based on how often one of my numbers has been highlighted through a table of alot of other numbers. I.e - say that A1 has the number in highlighted but also further along the table there is another one highlighted at J16 i would like to get a result of that.
    ive tried
    =COUNTIFS(Sheet1!U36:ZZ50,"15",Sheet1!U36:ZZ50,CountCellsByColor(Sheet1!U36:ZZ50,Sheet1!A16))
    where am i going wrong? thankyou in advance!

    1. Following this it would probably benefit to know my motive - i would like to know the number of time A21 (a number) comes up in the table d36:zz50 and has been marked yellow.

      1. I need something similar did you get an answer.....?

  9. Dear Concern
    How to used your formula with one more condition suppose i have different product in one column and there cashflow in second column and if the cashflow are highlighted by different colour as deffer and advance payment now i want for a particular product what is advance and what is deferred from huge data i cant get the required result from your formulas

  10. Dear Concern
    How to used your formula with one more condition suppose i have different product in one column and there cashflow in second column and if the cashflow are highlighted by different colour as deffer and advance payment now i want for a particular product what is advance and what is deferred from huge data i cant get the required result from your formulas

    1. I am looking for a similar forumula - trying to use SUMIFS with the countcellsbycolour - did you find a solution?

  11. Hello!
    Thanks for this amazing code!

  12. Hello, why diagonal border in M. Excel in conditional formatting not active, if possible review it and fix it and add it to conditional formatting
    thank you

  13. Thank you! The very best article related to this topic. Made my day.

  14. hi thanks for explaining this i am now a step further forward, however my sheet, i have a cell that changer color when past a certain date, when it does this i wish it to send an email to address listed on another cell (same row) with a pre set message.

    is there something i can add to this?

  15. I am trying to write a formula to calculate a golf handicap. I use a spreadsheet that records scores each week across a line for each player. I need to use only ten of twenty scores for each player. I want to use highlight colors to identify the ten scores to use and then add them across the line of scores as well as divide the total by 10 and subtract 36. all the info I can find is about totaling columns. Is there a way to add in a line?

  16. This is really helpful. Well explained.

  17. When I use the SumbyColor function, it does not prompt you to enter the different portions of the argument like with other functions that prompt you to put a comma which then shows you performing the other arguments.

  18. HI,
    This is an amazing code,but it always rounds up my numbers. Is there a way for me to have it so that it can sum by two decimal places? For Example, .05+1.2=1.25....

  19. Thank you!

  20. Thank you, this is exactly what I needed!

  21. What if I want to count cell colors with multiple range & criteria? I don't see that as an option here, and i'm not a coder. Would it be simple to add an IF function to this to accomplish this task?
    example: =CountCellsByColorIF($A$3:$A$12,"Yes",$B$3:$B$12,"Accepted",!A20)
    IF A=True & B=True, Then Count X Color

  22. Hi,
    I am using this function in a large table of data and I am sub-totalling by 11 colors. It works to begin with and is brilliant, but then the function keeps returning a N/A Value not available error. Do you know why? It seems unstable. I am in Excel 2016. Thanks a million. Darren

  23. Great code and helpful for work.

    I noticed that whenever I try to apply the VBA code to other workbooks (other tabs) in the same spreadsheet, I keep getting a #NAME? error. Now the error is applied to all my countcellsbycolor formulas/cells. Any help?

  24. Thank you very much it was really helpful. Thank you once again.

  25. I have a sheet with numbers scattered and colors.
    I want to count how many time a individual number appears with a particular color
    EG how many time the number one appears with a yellow background, or reg background
    How many times the nu
    mber 2 appears with a yellow background or red background

  26. Great bit of code, is there any way to count the number of cells with coloured text if a cell contains two or more different colours? i have a calendar on excel with events happening per day, if there are two or more events per day then the text is coloured differently within the same cell. I am looking for the number of events per colour.

  27. Once I save it all the figures they went to hash marks. What format do I save the counting cells as?

  28. Thank You so much. It works perfect. Thank you once again!

  29. Hello Svetlana and team.
    An excellent product with superb easy to follow descriptive documentation. Thanks and well done.

  30. thanks.

  31. Can I count the number of coloured cells with coloured formatting in a non consecutive range. For example if I were to wear blue socks on different days and colour the cells accordingly would I be able to count the number of times I had worn them on a particular day eg Monday.
    I guess the question is how do I stipulate the range I am asking the formula to address if the cells are not adjoining

  32. what about excel 2019???

  33. I used the instruction as give with the formula: =CountCellsByColor(B4:B69,B72)
    I received the error #NAME?
    When I ran Evaluate Formula function it showed #NAME?(B4:B69,B72)
    So it seems that despite me pasting the functions into the Visual Basic Editor it isn't recognising the CountCellsByColor function name
    Can anyone help me troubleshoot this please?

  34. I have tried the F2 and Enter process but it doesn't work - it just dims the screen. Any suggestions? Thanks.

  35. not working in row format

  36. Good formula and thank you for posting it. However, I would like to modify the counting macro to only count the cells by color that have a value > 0 in them. In other words, if I have 10 cells, but only 7 of them have data entered with a value > 0, I only want the formula to return the count of 7. How would I go about doing this?

  37. thank you so much, this was extremely helpful while i was creating a formulated leave planner for my account.

    however, if you could help me or is there are possible way to find out how to count the number of consecutive occurrences , meaning, in a month, if an employee AWOL's on the 12th, 13th and 14th
    the count will be 3 days, however, that is just 1 consecutive occurrence.

  38. Hi, thank you, this works just as I needed it, but I now have a problem. As soon as I save the file as xlsm file and re-open the file, all the formulas give me a #NAME? error in the place of the answers. What must I do to prevent this from happening?

  39. Thank you. It is very helpful formula.

  40. Thank you!!! Works like a charm on cells, except if cell contains an Array formula and cell background or font color is set via conditional formatting. In cells with Array formula it only sees all cells as having the default color.

  41. veeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeery usefull
    thanks lot

  42. I have downloaded and used the Ablebits Add-on successfully, Thank you for that. What I would like to do with that data is have it show up in a cell and be update-able. I have a spreadsheet that I am trying to count the colored cells that are out of range, I don't need an average of the cells just the number of red colored cells. I want that data to then be documented in a separate cell not just in a report on the side bar. My scenario is as follows: F5:AM5 (Conditionally Formatted to turn red if out of spec) & AO5 (Count Amount of conditionally formatted red cells out of spec)

    1. Hello, Chad,
      Thank you for contacting us and for your interest in our software.

      If you mean our Count and Sum by Color tool, yes, it can count cells by font or background color, but, unfortunately, it is not possible to automatically update the result of calculation when the number of colored cells change in the current version of the add-in.

  43. How to use the macro to count conditional formatted cell color in a formula, so the results show not as a comment?

  44. I've set up the macro, even downloaded Kutools and nothing seems to be able to count a range of a row (D3:D15) and give me a sum of either green, yellow, or red, conditionally formatted cells based on percentages. I've spent a few hours on this now, and have to revert back to counting manually. a copy of a portion to be counted is here, and need it as the 2nd recap.
    this is Jan thru Jun, all columns, but it won't paste as it looks in excel.
    Jan Feb Mar Apr May Jun

    103.4% 100.5% 101.8% 94.6% 99.3% 91.4%
    96.0% 93.6% 92.7% 96.1% 103.0% 100.1%
    77.3% 87.0% 80.7% 99.7% 94.9% 95.1%
    106.4% 100.6% 107.0% 103.1% 99.0% 107.1%
    91.5% 99.1% 72.8% 89.4% 106.9% 90.8%
    99.0% 75.4% 84.6% 93.3% 84.5% 78.2%
    my formula is this, to create the conditional format
    = 98.0% through 102.0% = green
    = 103-104% = yellow
    = 105.0% = red
    This is the recap i need to put in the macro to count whether Green, yellow, red.
    Green Yellow Red
    0 0
    0 12 0
    3 2 7
    3 2 7
    2 0 10
    1 0 11
    0 0 12
    1 1 10

  45. Hello l have vertical blank spaces say 15
    Then say a color (say red) that would end the blanks being counted
    I then have more vertical blank spaces say 3 (3 blank spaces) in the same vertical line or drop
    And then a color (say red again) to end the count and it continues
    Then more blanks then ended with a red (blanks counted and ended count with another red)
    Etc, etc, etc all different
    I would like to sum up the blanks then number them before it moves onto the next lot of blank cells and number them also on the last blank space (or the beginning) of each lot of blank cell
    In other words when it gets counted down (vertically) to the last blank cell before the color in a cell it will sum it up.
    Then when I have a list of multiple blank results of the blank cells in block l want to work out the average blanks of all of them.

    In other words sum up the blanks that are broken up by the reds (it could be a different colour)

    Thank you

    Slane

    1. Hello, Slane,

      Our technical specialist took a look at your task. Unfortunately, there is no easy way to solve it. Most likely you need a special macro. I am really sorry we can’t help you with this.

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

      Sorry for not being able to help you better at the moment.

  46. Thank you so much for this code!
    I do not know what I would do without it!!!!

    Very very helpful... once it all works out!!!

    A tip that helped me (not a coder) was to know that I could click any cell that had the color I was referencing in the formula (=CountCellsByColor(range, color code) )

    easy peasy!!! Thanks I will be sharing

  47. Hi,

    I'm still returning #Name? after inputting the VBA into the module. It seems the sumcount via Alt+F8 seems to work, however when I try to count the conditionally formatted cells via colour it returns #Name?

    Any advice or suggestions where I'm messing up?!

  48. Its really good but i wants to know if i change the colour then it can do auto change in sum

    1. Hello, Rikin,

      Please note that the sum and count of the colored cells won't get recalculated automatically. Once you've made all the necessary changes, simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated.

      1. Hi,
        Is there a way to set up sumcellsbycolour so it calculates automatically? I have calculation options set up as automatic but this particular formula is not updating automatically. this formula works great otherwise.
        thank you
        Fiona

        1. the closest thing I can suggest is to have VBA code that updates on selection change, more on that here https://trumpexcel.com/vba-events/#Worksheet-Change-Event

          However, you have to move click the cursor somewhere before excel will recalculate

  49. Thank you!
    I've just discovered your website, blog, and products. They're terrific.

  50. I'm getting a syntax error on "Dim indRefColor As Long".

    1. As do I.
      "Compile error:
      Syntax error"
      I copy-pasted the code in to my own document and I still get that error. Doesn't get it with the downloaded example document, and can't figure out what the difference is!

      1. Hi Christopher / Anton, I believe the code in this page had some syntax issues. Please download the sample document from this page. Go to VBA module and copy-paste the code to your sheet.

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