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

  1. Hi, THank you for posting this - I've not been able to find the right UDF to count the conditional formatted cell colors. I'm new to creating UDF in the VBA editor and wanted to doublecheck - Do I need to paste the FIRST code and THEN the 2nd code directly beneath to get the full conditional formatting color count ulitization?

    thanks!

    1. Hi Meg,

      To count and sum conditionally formatted cells, you don't need the first code, only the second one.

      Also, please pay attention that the second code is a macro (the first one is a UDF), and both work in modern versions of Excel 2010, 2013 and Excel 2016 only.

  2. Svetlana Cheusheva,

    Thank you for the article. It was really helpful and I do understand it all. Although, the worksheet/workbook I build utilizes conditional formatting "Formula is equal to" with relative reference not absolute and therefore returns the same value for the cells interior color even though they visually look different. I would like to count the number of cells that are equal to a particular color but after reading: http://www.cpearson.com/excel/cfcolors.htm
    the ActivCondition function will not work. Is there another way this can be done? I can provide copy of the worksheet/workbook if need be. Thank you for your help.

  3. What a waste of time. It doesn't work.

  4. Hello there! In need of some help... Can I combine the countcellsbycolor function with a countifs function? I would like to only count if a cell as a certain text and is colored. Any help?

    1. Hi Cristina,

      To count cells with several conditions including the cell's color, you will need to use the GetCellColor function as part of an array SUM formula. Please see my response to comment 89 for a formula example.

  5. Thanks. This was exactly what I needed in terms of utility, explanation, and how-to steps. I very much appreciate this.

    Sincerely,
    Tom

  6. Hi there! I have a color-coded table and need help. The table has a person listed for each row and various categories for the columns. I need to count how many people have a green-colored cell in any column next to their name, but if they have more that one green it should only count once. Additionally, if they have at least one green cell in their row, then these people should be ignored when tallying the number of people with at least one blue or yellow in their row . How would I do this?

  7. Thanks a Lot it helped me

  8. After everything we have done....wen i run the coding it says compile error : syntax error....y so..?

  9. You guys are friggin' awesome! Thank you for posting this and the related "Change the row color based on cell value" article. So helpful.

  10. Please help. I have different sheets with staff name verticl and horizontal 31 colomns and cells in all different colors, I want to create a final sheet that will count colors

  11. Hi,

    I'm trying to use your CountCellsByColor code and seems to work fine when I enter the color in the cells manually, but I have one small problem. It seems to be designed to measure only manually entered colors within the cells, and in my particular case I have multiple values in the columns, which are highlighted in different colors when repeated (by using the conditional formatting - Highlight cells that contain text option) - so technically speaking, the color values are automatically populated and not manually, therefore the formula doesn't quite count them correctly. Any advice on what I should do here?

  12. Thank you!
    Works perfect and helped me a lot!
    Good job!

  13. Works perfectly! Thank you!

  14. Thank you for your answer.
    Is this the still the case in Excel 2013?
    If yes, let's hope Microsoft will fix this.

    1. I am afraid it is. I consulted Alex (our Excel guru who wrote these UDFs) and he says Microsoft actually promised to fix this. So, let's hope :)

  15. Hi
    I have used your code to count by color and sum cells colored using conditional formatting and it seems to work great.

    However, Is it possible to use a similar code to put the result of count or sum in a designated cell rather than in a pop up box.

    Thanks,

  16. Thank you very much, this is great

  17. Svetlana,
    I do not understand why the sub SumCountByConditionalFormat() can't be rewritten to a function.
    I did it (stubbornly trying) and -of course, as you explained- it did not work.
    The function just halts on the command
    indRefColor = refCell.Cells(1, 1).DisplayFormat.Interior.Color
    I mean, really halts, no further commands are executed.
    No error message, nothing, the function just halts and the cell where i put the function call gets "#VALUE!".
    Why is this?
    Why does .DisplayFormat.Interior.Color work on "ActiveCell" but not on "refCell.Cells(1, 1)"?
    And why no error message?

  18. Thanks for posting this, the instructions work well and make it very simple to implement. Worked perfectly in my spreadsheet!

  19. Hi
    I want to be able to summarise data in cells located in diverse places on the workbook; ie not in a single table or column or row etc.

    Perhaps if I could tag or mark the cells containing the relevant data I want to summarise so that the cell which displays the summary output would look for all relevantly marked cells in the workbook and treat them as if they were all in a single column or row or table.

    Is something like this possible?

  20. I am trying to count the number of times cells in a contiguous range match a certain color AND contain certain text. I'm using this for a football offensive play script where I highlight a cell yellow if that position is getting the ball on a particular play, and type the name of that player in the cell. I'm trying to calculate how many times each player is getting the ball based on the criteria that the cell would be filled yellow and have their name in it. I've been trying to use a combination of your code plus a countifs, but can't get it to work.

  21. Absolutely fantastic, have wanted to be able to do this for years (rather sadly I know). Great to discover this resource, all signed up now.

  22. I have Excel 2013 and cannot figure out how to post the code. When I click on Alt-F11 I do not see Visual Basic Editor--instead, a box appears on top of the workbook with pictures of everything that is currently open on my desktop. If I choose and click on the picture of the workbook that I'm trying to make changes to, it just then opens as the normal workbook. I want to both count the number of cells that are colored, and separately sum the amounts based on the various colors. Is Excel 2013 handled differently, and if so, can you help?

  23. Excellent work, thank you very much to share something really helpful and interesting too. :)

  24. Kudos to you all.

    Same question as Vicki asked on 24.02.2015. How can we transfer the VB code for all other Workbooks?

  25. Thanks for the info, worked very well. How can I transfer the same macro to other workbooks?

  26. I have a spread sheet with few coloured cells across different columns ( Red a, amber and green )

    If i had to see only red coloured one what is the formulae

  27. ALL sorted...thanks anyway!

  28. Hi again Svetlana,

    I have made the changes to suit the comments for Brian's comments above and it works great for SPECIFIC text. However if I change the search for a specific text to the search for any text, as in "*" then it returns nothing.

    Any help would be appreciated

    Thanks

    Lee

  29. Hi Svetlana,

    Thanks so much for this work!

    Just one thing though. Is it possible to count cells that are of a certain colour but also contain a cell entry?
    Where there are empty cells I still get a returned value based on the pre formatting.

    Also it cause an issue with Merged cells as if 4 cells are merged together you get the returned value of 4!

    Thanks in advance

    Lee

  30. Hi, I have used the above code in the VBA editor to enable the =CountCellsByColor code. When I use the code written as well as the formula provided it comes back with a 'NAME' error. I was hoping I could send my workbook for you to have a look at! Thanks :)

  31. Many Thanks

    Its working!

  32. Hello.

    Great post - I have one problem though. When i use this code in my worksheet, it only works if i give my cells a color directly. If i use Coditional Formatting, on my cells, and use this to change the colors, it does not work. Is there anyway where i can use the color generated by Coditional Formatting, instead of using the generic color which would be white.

    Please Help me! :D

  33. Thank you so much! Have not programmed in years and this was an easy solution to an otherwise very complicated and time-consuming task!

  34. hello,
    how to count non-blank colored cells???

  35. Thank you

  36. I know this has been mentioned before (so I've tried doing my own research) but when I paste this I can't find it when I go to run it. I've created some basic ones to check that it's not something wrong with my excel and they all work fine.

    Can't seem to find anyone with a solution to this, but clearly it must be me as it's working for everyone else! any suggestions? using excel 2010

    Many thanks

  37. Hello, my question above might not have been clear..
    My example

    row: 1-Jan, 2-Jan, 3-Jan, 4-Jan 5-Jan
    1-Jan and 2-Jan are highlighted green. The rest of the dates have no highlight.

    I want to look at only the green dates and find the latest (Max).
    (so 2-Jan)

    Thank you for any guidance on this!

  38. Other than sum, count...

    Can I identify the max value in a range of colored cells in my row?

    (my green cell is an "achieved" date. I need the latest achieved date in my row...multiple other cells are of other colors)

    *Manually formatted cells
    thanks!

  39. hello,
    It was very helpful,
    thank you

  40. Your VBA is just what I've been looking for. However, I'm having a recurring "crashing" problem with Excel 2013. The code has been inserted as a Module verbatim from your site. I don't have to press F2 to refresh the values even tho lots of posts say I should. The VBA seems to recalculate my counts whenever I manually update the cell fill color. But it runs very, very slow. I can see the processor demand running in the bottom info bar (4 processors - running slow). Any clues as to why this would be running so slow? I'm running 64-bit Windows 7 with 8GB RAM. Is the VBA having trouble with the 64-bit OS perhaps?

    1. Same problem here, have you find another way? or find a way to fix the slow problem?

  41. Hi

    have just used this in one document. However I get the error message "ambiguos name detected count cells by color". What have I done or not done?

    many thanks L

  42. Hi,

    I'm using Conditional Formatting to color my Cells. So, when I use the "GetCellColor", they call have the same value (5296274).

    So, in my Conditional Formatting, I have cells turn red if the date in a different cell is over 1 year and green if it's under one year.

    Is there any way to use the "CountCellbyColor" for Conditional Formatting?

    Thanks,
    Bruce

    1. Hi Bruce,

      Regrettably, there is no reliable way to get a cell's color in a custom function when this color was applied using conditional formatting.

  43. Thank you very much for the count by colour (if manually applied) code. Works brilliant and has really helped me in my job.

  44. Hi Svetlana,

    Fantastic and elegant code. Many thanks. Some people are just TOO clever. It's so great to have free access to such gems. Very generous.

    Best regards,
    Bobbler

  45. Hi Svetlana
    Thanks for sharing wonderful code, i just used it and saved a lot of time.

    Thanks again!

  46. Very good stuff, does it work on conditional format cell? I have tried but failed to do so.

  47. Amazing functions,
    but i have a query please:

    it's a little bit strange question, but how it can works on columns basis, to be more clear if you merge 4 cells horizontally it will show only 1!!!

  48. Thanks so much for this! Saved me much time and effort! Have a great day! :)
    Marc

  49. Hello!
    Thanks for this amazing code! I copied and pasted it in, but when I try and use the formula it just gives me the following ...#NAME?

    Can you tell me where I'm going wrong?

    1. Hi Laura,

      Got the same problem. I ensured that I saved the file with macros enabled (xlsm format via File | Save As...) and then in the IDE (Alt+F11), I ensured that the functions were part of my workbook (they were originally in PERSONAL.XLSB and possibly not in my file.xlsm). Finally, I saved the file and was then able to use the functions. Hope this helps you.

      The functions take a while to process as little as 1024 fields in a row with 7 different colors but it works great!

      Marc

      1. I got the same problem as Laura. So I made sure the functions were in the IDE workbook then changed the settings in the Trust Center to enable all macros and saved the file as xlsm, closed and opened the file again. Still got the ...#NAME. What is happening?

  50. Hi!
    I love this function, you did a very good job coming up with it!

    I am having an issue though.. I am using the first code you posted to count the number of cells coloured a certain shade in a specific column. It works great, until I change a cell's colour and the number in the function cell doesn't change -- I need to go to that cell and run the code again to see the up-to-date sum.

    Is this what you mean by 'manually' ?
    Can I fix this?

    Thank you!

    1. Hi Elle!

      By default the code doesn't track coloring of new cells. If you need to automatically update the result as soon as you color another cell, please use the Count & Sum by Color tool:
      https://www.ablebits.com/excel-count-sum-color/index.php

      Alternatively, you can use the code posted in response to comment #44.

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