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

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

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

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

  4. Kudos to you all.

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

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

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

  7. ALL sorted...thanks anyway!

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

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

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

  11. Many Thanks

    Its working!

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

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

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

  15. Thank you

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

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

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

  19. hello,
    It was very helpful,
    thank you

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

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

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

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

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

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

    Thanks again!

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

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

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

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

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

  31. Hi This Article is really usefull and saved a day to me..

    Thanks for posting this...

  32. The Code is useful but when I saved it as xlsm format and reopen it the next day the Calculation is gone. I would need to add in the code again and recalculate again.

    Please advise if it can be saved in the document itself?

    Thanks and regards,
    Rebecca

    1. Rebecca,
      When you open it up just press enable macro again and it recalulates

      Nige

  33. Excellent code and even better follow up assistance, from what I can see above at least.

    I a using the SumCellsByColor command code to prepare a timesheet for employees working on different jobs using the colors to sum the different 'types' of time (Normal, OT, Onshore, Offshore). I have individual tabs assigned for each employee and am using a separate 'summary' tab to bring all the information together. It is easy to prepare a summary for each employee and the type of time (using your code). However I also want to track the time spent on individual job numbers (job numbers are marked as lines on each tab with the columns being dates).

    Basically, as it is possible that each job is worked on by separate employees, I want to be able to show what type of time is being spent on each job number on my 'summary' tab.

    Can you make any sense of that? I think I may have even confused myself! haha!

    Any assistance is much appreciated!

    Thanks & Regards,

    David

  34. Really a handy tool. Saved a lot of time :-)
    Thanks for sharing it!

  35. Hello,

    First off, I love this code. I am brand new to macros and this walk-through helped me out tremendously, so thank you! I would like to use this macro with another formula, if possible. Here is what I am looking for:

    I am comparing data across multiple sheets based on the color of the cells to indicate what sales stage the contract is in as well as their expiration dates. For the report I'm trying to create, I have columns in place to show which contracts will expire 120 days from today, 90 days from today, 60 days from today, and 30 days from today. The rows indicate the sales stage that the contract is in. The columns are based on an IF statement using TODAY as the volatile factor and the rows are based on the lovely macro you have created, using each cell color as the indicator of which specific sales stage the contract is in. Since the first row indicates that the contract is in a Not Contacted state, I would like to have each row below that not only count up the number of cells that have a specific expiration date and background color, but also subtract from the Not Contacted row.

    I feel like this would require an IF statement within the macro, however I am so new to macros that I am not really sure where to begin with this one. If my explanation is confusing at all, I apologize. Any and all help is appreciated!

    Thanks,
    Antionette

  36. I can't get the macro to show when I press Alt+F8. I copied the code multiple times.

  37. This code has worked wonderfully.

    Thank you!

  38. This code was a god send... It worked wonderfully and has sped up the time it takes to complete a report I have to do each month by at least 50%

    Thanks :)

  39. This code has worked wonderfully, thank you! I only have one issue. When I saved the workbook, I saved it as a macro-enabled workbook as you instructed. I have opened the spreadsheet, and the macros are enabled because the formulas all work correctly. However...

    There is a graph in my workbook, and now - when I click on the chart - it has an error that says "Cannot run the macro "name of chart_Click'. The macro may not be available in this workbook or all macros may be disabled." Do you know why I would get this error?

    Thank you!

  40. Thank you for this code! It is great. I see it only works when I have the macro open even though I saved it as you advised. Is there anyway to make this a permanent function so that I won't have to open the macro every time?
    thanks!

  41. hello there, wondering if I can get some assistance. I want to be able to add all "points" from each location in one colum, however, each month the place of each location will very, so i want to be able to get a formula where it can be added no matter where the location number is at.

    this is kind of an example of data am working with.

    Location Quota Points Location Quota Points LocationPoints
    4014 141.00% 20 4006 46.00% 20 4006
    4022 141.00% 16 4016 44.00% 16 4016
    4003 130.00% 15 4022 43.00% 15 4003
    4016 127.00% 14 4014 42.00% 14 4014
    4006 126.00% 13 4003 41.00% 13 4022

    is there a way to figure add this up with out having to arrange them in least to greater?

    1. Hello Fabiola,

      It is difficult to understand your task because your data are distorted in the comment. If you can send us your workbook at support@ablebits.com and include the result you want to achieve, we'll try to work our a proper formula.

  42. Hi, I used the VBA code and formula to count colored cells. I have 6 colors. Four of them calculate correctly, but 2 do not (extra cells are counted). What can I do to correct the error?

    Thank you.

    1. Hi Kayla,

      It is difficult to say why this happens without seeing your data. Please send your workbook at support@ablebits.com and we'll look into the issue. Also, please check if the specified range doesn't include the cell that you chose as a color sample.

      1. Thank you, I've just emailed Support.

        1. Hi Kayla,

          Thank you for your worksheet.

          The point is that your worksheet contains some merged cells, and our formulas count each individual cell that was merged.

  43. I just tried this macro and it works well. thanks you

  44. Is there a way to count cells of a specific color and containing a specific text? Say the name Bill appears on a sheet 10 times and 5 of the cells have been manually colored red. Is there a way to count how many times cells containing the text "Bill" and background filled red?

    1. Hi Brian,

      Yes, this is possible now, we've updated the code of the functions. Please copy the updated code to your workbook and then enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM((A1:B13="Bill")*(GetCellColor(A1:B13)=GetCellColor(D1)))

      Where A1:B13 is the range where you want to count the occurrence of the word "Bill", and
      D1 is the helper cell with the color sample, e.g. red.

      You can also type =GetCellColor(D1) in any cell, copy the result and paste it to your formula instead of calling GetCellColor(D1). In this case you don't need the helper cell.

    2. =COUNTIFS(A1:L326, A330, A1:L326, getcellcolor(A330))

      Tried this and got "0" when, in fact there were 2 that met the criteria.

      A330 is a text value of a person's name as well as being filled with the reference color. I have tried it with "text value of 330" and "=text value of A330".

  45. I have implemented your script and the patch to actively update the cell data and it works perfectly. When I reference that data in another cell it doesn't update the data without me forcing the update. By the way Ctrl+Alt+F9 will update the cells. No need to click in the cells to accomplish the data refresh. Is there another line I can add to the script to keep the referenced data active as well?

    1. Hi Brian,

      Can you please send your sample workbook to Alex (alex@ablebits.com) and describe the use case step-by-step. In this way it will be easier for us to reproduce the scenario and suggest a fix.

  46. Great stuff! Saved me a lot of time!!

    Thanks a ton.

  47. hi

    how to protect the particular column and cell in the excel 2007
    i dont want whole sheet protect only particular cell i want

  48. Hi Svetlana,

    I thank you for the amazing tool. It is working well.
    I wanted to find out how do i count colored merged cells. I am creating a dashboard for data.
    Can you please help me.

    I thank you

    1. Hi Arshad,

      We have not tested the code on merged cells, but in theory it should work properly, i.e. a merged cell shall be calculated as one cell. Does function behave differently on your data?

  49. Hi!

    Thank you for this script! I had problems when I tried to use it on Excel 2010. Excel said that it isn't valid formula and it has some errors.

    But then I replaced "," --> ";" and it worked (like this: =SumCellsByColor(D2:D14;A17).

    1. Hi!

      Most likely this is because you have the List Separator set to ";" in your Windows Regional Settings, so you should use a semicolon in all of your Excel formulas.

      1. I think it would be quite beneficial to mention this comma vs. semicolon option in the tutorial. It would have saved me a lot of time.. Thanks for the functions anyway, now that I got them working by replacing "," with ";" :)

  50. Just to follow up on my previous comment. I had already found http://www.get-digital-help.com/2013/10/02/counting-conditionally-formatted-cells-vba/ which works great in their example. We have pretty complicated conditional formatting on multiple cells. If I choose more than a row or a column or have a cell in the range that does not have the conditional formatting, I get a #Value. I do not really understand why theirs works as a function, but in a limited fashion, and yours works all the time as a sub...

    1. Hello Shelli,

      I have not tested their code, but it looks like it works only with conditional formatting rules based on formulas that changed the cell's background color, if true. I am not sure it will work with color scales. This code should also be checked with regard to min/max/duplicates.

      Also, the code implies that each conditional formatting rule found in the selected range applies to ALL selected cells. If it is not so, then the code will return #Value or even throw some VBA execution error.

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