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

  1. veeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeery usefull
    thanks lot

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

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

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

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

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

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

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

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

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

  11. Hi

    Your code works perfectly - thank you. I understand that you can't create a function that will count cells for all types of conditional formatting and output the results to a cell. But if you were to constrain so it assumes one kind of conditional formatting, would that be possible? I only ever use conditional formatting based on cell contents or formulas and I can usually choose either. Would it be possible to create a function that worked with one of those types?

    Thanks
    Paul

  12. Hi Svetlana,
    Wonderful code, working perfectly! I just found one issue - when I delete something or leave an empty cell within the region it is still counted by CountCellsByFontColor.
    Any idea how to overcome this? How can I count only non-empty cells based on the font color
    Thanks,

  13. Hi,

    Thank you for the codes.
    I have used the code for the count by conditionally formatted color, and it works. But rather than the information being shown in a message box, I want the information to appear on a cell. Is this possible? Can I amend the code in a way to accommodate this?

    1. How were you able to count by conditionally formatted colour cells? I've been struggling with me returns!

      1. Hello, Khalid.

        It looks like our Count and Sum by Color add-in can help you with your task. The tool can help you sum and counts cells by font or background color in a few clicks. If you want to see how the Count and Sum by Color add-in works, feel free to install the fully functional 14-day trial version of Ultimate Suite that contains all our products for Excel (70+ add-ins) using this direct download link.

        After installation you can find the add-in in the Calculate group under the Ablebits Tools tab. If you need the instructions how to use the add-in, you can find them on its help page.

  14. Hi, I would like to count cell color of individual worksheet on a master sheet. Each worksheet must be separate.

  15. Doesn't work. Just keep saying 0. followed everything, word for word. The file is even saved as macro ready. Even played with the formatting to match what I was looking for. Nada. I spent over an hour trying to figure out why. I went as far as using a new workbook completely empty starting from scratch to see where I went wrong. Still nothing. I can't figure this out. I'll keep looking online.

  16. super helpful and very quick to apply! Thanks.

  17. Good article.....Keep going

  18. The count cells by conditional format is fantastic. I have been searching the web for over a year for a good solution. I have another twist to this. I have a very large data set that should be unique numbers. I can set a conditional format to look for duplicates but then I need to find them, if they exist. Is it possible to adapt the code to find the first cell that is conditionally formatted (if there is one) and then use this cell to set the format to count possible additional entries in the entire dataset?

  19. So legit I actually took a moment to thank you for this little piece. Just what I needed!

  20. i want to know how to autocount all the numbers insid the color cells

  21. superb

  22. Function works great - just not on cells using conditional formation.

    Is there way to count cells by colour if the colour is assigned by conditional formatting?

  23. Very helpful, great instruction!! Thank you.

  24. functions are not working on conditional formation

    1. I am having the same issue

  25. Is there a way to use the countcellsbycolor function on a filtered data set? (excluding the hidden data)

    1. Try subtotal function.

  26. Hi,

    I have data where i need to insert sum formula for perticular font coloure rows to sum its above data till the coloure change.

    please help me with suitable macro or formula.

  27. Very helpful. thank you.

  28. Ahmed; taken from the article
    "Note: If after applying the above mentioned VBA code you would need to color a few more cells manually, the sum and count of the colored cells won't get recalculated automatically to reflect the changes... simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated. The same applies to the other macros you will find further in this article."

  29. frist of all thank u for this good data
    but if i change any color in range it doesn’t be counted and the result didn’t updated? i want to count cells by color even i change the color in the range

  30. How to count by font colors, and within range, for example I only want to count, 12345, I only want to count 2 and 4, can I put ( "2")

  31. I used this for a workbook with manually changed cell colours and everything is working but the numbers do not update when I change the cell colours. For example if I change one cell from green to blue, the count for each colour remains the same as it was before. Is there something I can do to fix this?

  32. Code works flawless on how it's built. What I am trying add/change, is how can I "exclude" negative numbers?

    Example code on one of my columns:

    =SumCellsByColor(T7:T500,T11)

    What I want to do is IGNORE any negative numbers.

    I was trying this:
    =SumCellsByColor(T7:T500,T11,">0")

    But that gives the "#VALUE" error.

    Thanks

  33. After adding the code, there is an advertisement of UBit Schweiz AG appear to all office software. How to remove that??Thanks

  34. Does this work for Google Sheets as well? It worked wonderfully in Excel.

  35. Hi,
    It is working. Have a little bit doubt. Can it be used to count colored cell if a conditional formatting is applied? I could not achieve this. Is there any solution for this. Please Help me.

    Regards
    Shani

  36. Hi,

    Been using your code for several months now and works really good, until today, there is an error.

    it says Runtime Error 438. "Error 438"

    Could you help me to fix it?

    Thanks

  37. i tried this on worksheet where cell contains numerical values. =CountCellsByColor(F2:F14,A17) formula is not taking count ,pls help

  38. Hai Dear,
    I can see, everybody is using this stuff in very friendly and good, unfortunately its is not working with me, may am not much expert in Excel. Please any one can help me to find the solution.

    I filled with 4 different colours (RED, BLACK, YELLOW & GREEN) from A1 to F55, my requirement is need to find how many Red, Black,Yellow & Green.

    Someone can help me for this??

  39. good job! but when using other kind of conditional formatting such as "top 10 item" or "color scale" or "icon set", the macro doesn't work. do you have a solution for these kind of conditional formatting?
    thank you

  40. =countcellsbycolor(range,criteria)
    it is not working
    i want to count colored cells and font please help me in this matter
    thanks

  41. Hi,

    The VBA works brilliantly when I manually colour the cells, but it is not work with cells that have been condtionally formatted. Any suggestions?

    Can you help

  42. Dear Ablebits

    Thanks for your work! When I run use the countcellbycolor function it returns an error:

    Compile error: Syntax error

    The debugger points to this line:
    Dim indRefColor As Long

    I'm using the code in Excel 2016 on Windows 10. A number of other people have the same problem. Would you be able to resolve it?

  43. Another thing I struggle with is the syntax of:
    =INDEX(C3:C520,MATCH(0,COUNTIF(C3:C520),AA323))

    I want a count of unique names in column C that matches the criteria in AA323.

    I'm trying to show # of DBs in resolved status when DB Name and Status are in separate columns.

  44. The code did well for simply counting cells by color, but my needs are more complicated. I'd like to learn how you set up the table color coding in the Delivery Column. I'm trying to use a date entered to set a Due Date and color code it yellow after 15 days past due and red after 30, THEN count cells by color.

    1. James:
      Can you provide some samples?
      Also, what is the relationship between the Due Date and the date entered?

      1. Decommission Ticket Dt Past Due Date(=IF(ISBLANK(V3)=FALSE,(V3+45))
        FALSE
        05/01/2018 06/15/2018

        Generated due date should be 45 days after the ticket date.
        When the due date turns 15 days past due, it should turn yellow (Cell Value > =$Z$26+15), 30 turns red. False remains white. I'm using Conditional Formatting for this.

        So far, generating the dates is the easy part and I can singally affect a past due date with color but it all jacks up when I bring it down the column.+

  45. bless you!

  46. Is it possible to count a coloured cell by contents.

    Example:
    B2 contains the text 'E1' with no fill and cells B3 and B4 contains 'E1' with a blue fill. B5 is a blank a blue cell.

    In B6 I would expect the answer to be 2 as I only want to count the blue fill cells with E1....

  47. Thank You so much for putting this out there! Was just what I was looking for and more!

  48. Awesome, thanks very detailed and works perfectly, not often when I try one of the solutions i searched for does it work first time!!!!!! This does THANKS

  49. Many Thanks. I tried a half dozen different solutions from as many websites and this is the only one that worked flawlessly.

  50. I'm having an issue that I can’t work out that combines applying Conditional Formatting to number cells (the amount paid) based on the date in the cell to the left of them (the date the payment was made). This date needs to be between the dates stated in other cells i.e. 'check that date is greater than date in cell ai10 but less than that in aj10. If it meets that criteria then I want it to colour the amount paid cell the colour I have selected in the Conditional Formatting. Can anyone help?

    I have used the above VBA and formula in this article which looks like it is working so that once I have the above sorted and the cells are automatically coloured I can then get a total amount paid between the 2 stated dates.

    Thank You
    Simon

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