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

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

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

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

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

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

    Thanks again!

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

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

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

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

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

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

    Thanks for posting this...

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

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

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

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

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

  17. This code has worked wonderfully.

    Thank you!

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

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

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

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

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

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

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

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

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

    Thanks a ton.

  27. hi

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

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

  29. 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 ";" :)

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

  31. So confused about the "How to count by color and sum cells colored using conditional formatting" needing to be a sub and not a function. Not sure I understand what the limitation is. I am still playing with it. Works perfectly as a sub but need to get the count in a cell.

    1. Hello Shelli,

      Regrettably, there is no easy way to implement this as a function. I mean the function that would correctly handle all possible variations of conditional formatting. This would require a great deal of VBA code, tedious debugging and testing against different Excel versions and various Office localizations. (That is why the other solution you've tried works in simple scenarios and does not work with complex conditional formatting).

      So, our Excel specialists have weighed the pros and cons and created the Count & Sum add-in, which caters for all possible scenarios and works correctly with all Excel versions and all kinds of conditional formatting. You can download the evaluation version and try it out on your data.

  32. Great stuff! Exactly what I was looking for...

    Thank you so much!

  33. Many thanks for this article. The instructions, code and functionality were excellent and exactly what I was looking for.

  34. Or better still - how can I build a sumif formula around coloured cells. For example =sumif(range, [CellColour], [sum_range])?

    1. You can use the SumCellsByColor function to sum the values of cells of a given color in the selected range. Or, are you looking for something different?

      1. Hi Svetlana,

        Yes, looking for something a bit different, the cells that contain the values I want to sum are in a different row to the coloured cells so would be looking to do a calculation similar to the sumif function

        Cheers

        1. Hi Wazza,

          Thank you for the clarification, I understand now. Regrettably, this is not possible because our functions do not support array formulas.

  35. This is genius. However, would vlookup and hlookup by cell colour be possible. For example, =vlookup([CellColour],table_array, column_index_num, [range_lookup])?

    1. Hello Wazza,

      You need to add only one function to your workbook – GetCellColor. Then add an additional column to your lookup table, this should be the first, i.e. left-most column, and copy the following formula to it: =GetCellColor(F2). F2 is the needed cell color.

      An then, you use this formula =vlookup(GetCellColor(A1).... where A1 is the color-coded cell.

  36. Thanks for the prompt response. It worked. Hopefully, the add in that you / Aex is developing will work in both scenarios. Keep up the good work guys. Thanks

    1. Hi Samir,

      Our new add-in, Count & Sum by Color, is finished and available for download. And yes, it works in both scenarios and in all versions of Excel!

  37. sorry - im new to excel :|

    here's an example - i wanted it to count only "red" when Number is filtered to "1"

    A B C D Number
    red green orange 1
    orange red green 2
    orange red green 3
    red green orange 1
    orange red green 2
    red green orange 3

    i believe this can be achieved by selecting the range manually but is there a way to do this without doing it manually?

    1. Hi Dniens,

      Our code counts all cells in the selected range, including filtered and hidden cells. To be able to tell you with certainty if we can help you achieve the result you want, we need to see your data. If you can send us (alex@ablebits.com) your sample workbook and the desired result,we will try to help.

  38. This helped me a lot.. thanks..

    just one more thing on "How to count by color and sum cells colored using conditional formatting" - is there a way this can only count the filtered values? (i.e.. just count the QTY only for the PAST DUE on the Delivery column)

    1. here's an example.. when i filter "1" it still counts the hidden cells... i just want it to only count "red" in number "1"

      A B C D Number
      red green orange 1
      orange red green 2
      orange red green 3
      red green orange 1
      orange red green 2
      red green orange 3

  39. Thanks for the Article -
    The spreadsheet I have contains both manual and conditional formatting in the same column,to provide same color - Red background color in this case - now the code that is written addresses either one of the two cases - ie either you can do a manual count or count by conditional formatting; is there a code that combines both cases in one so it can work in my situation.
    Also, do you know if there is a way to subtotal that can provide accurate count if the spreadsheet is filtered based on a certain criteria;

    Thanks for checking.
    -Samir

    1. Hello Samir,

      The Conditional Formatting code also counts and sums cells colored manually. As for subtotal, sorry we do have any idea.

      1. Hi Svetlana, I found your article very helpful. I am also looking for a way to subtotal the count of cells by color for sheets that require filtering. Is there any update on this? Thanks in advance!

  40. My chart did not post as set up.

    Company/Group #/Eff Date/Term Date/Jan/Feb/March/April (each is a column)

  41. I have a very large spreadsheet with several columns, see example below. Each month I need to count active Companies(color coded by location) for example; in January column, only count Companies that are blue and active, in February column only those that are blue and active, etc. Then repeat for Companies coded purple.

    I was able to use your code above to add by color, but now I need to eliminate those that have terminated.

    Company Group # Eff Date Term Date January February March April
    ---------------------------------------------------------------------------
    ABC Co. 123 1/1/10 1/1/14 $0 $0 $0 $0
    123 Co. 456 4/1/09 $20 $20 $20 $20
    D's Bar 789 2/1/07 2/1/14 $15 $0 $0 $0
    PJ's Pub 654 1/1/14 $45 $45 $45 $45
    Pizza Pub 321 1/1/13 $10 $10 $10 $10
    Day Center 852 1/1/12 3/1/14 $25 $25 $0 $0

    ABC, 123, D's Bar are Blue - therefore result in January s/b 2, February s/b 1, March s/b 1.

    Pj's Pub, Pizza Pub, Day Center are purple - therefore result in Jan s/b 3, Feb s/b 3, March s/b 2.

    Is there a way in January to countif blue and term date is after 12/31/13? Then in February countif blue and term date is after to 1/31/14?

    I need to read by date rather than $0 as sometimes commission is received even though terminated.

    Thank you in advance for any help you can provide, as I am very new at this.

    1. Hello Wendy,

      Yes it is possible. Please send us a sample workbook at alex@ablebits.com, and we will work out the solution.

      1. I have a similar situation; is there any way you can publish and example of how to do this?

        In my case, the sheet isn't very big, I'm just trying to automate things as much as possible so that data can be updated when I'm not there to adjust things. I need to be able to sum all cells of a certain color that are adjacent to cells containing specific text.

        (I have a series of quantities in column A, and quarters - Q1 2016, Q2 2016, etc. - in column B. I need to sum only those quantities that occur next to a specific quarter, and are of a specific color.)

  42. Good Morning,

    Is there a way to count by background color only if it is prior to a specific date?

  43. When it brings me back to the module I inserted it highlights the very first GetCellColor in the module.

    Thanks again!

    1. Hi Janis,

      Please make sure that you pasted the complete code in the module. The compile error usually indicates that some part of the code is missing. Please verify that the code starts with "Sub" and ends with " End sub". You can also download this sample workbook with the CountCellsByColor and SumtCellsByColor functions ready for use and try them on your data.

  44. Hi

    I posted the 3 modules you provided in my workbook. I saved it as a .xlsm file type. When I try to enter the formula - GetCellColor or CoutCellsBy Color - it brings me back to my modules coded with the error:

    Compile error:
    Expected:end of statement

    I don't know how to fix this.

    Thanks for the help!

  45. This code works great....I could use one additional function. I need to check and see if a cell has data in it before I count it.

    1. Thank you Dennis, please let me know if I can help in any way.

  46. The result I get adds the totals of all the cells in the row that are being conditionally formatted. I have 3 different conditional formats, I understood that the count of sum function would only act on those that met the format of the cell I wanted summed or counted. What am I doing incorrectly or does the formula not act on the specific formatting of the cells in question? I can send the file to you if you'd like to view it. Thank you.

    1. Hi Jerry,
      The =CountCellsByColor() and =SumCellsByColor functions count only cells of a specified color. For example, =CountCellsByColor(F2:F14,A17) will color the cells in the range F2:F14 that are of the same color as cell A17. If it does not work in this way for you, please do send us your workbook at support@ablebits.com.

  47. Hi! I'm trying to count by color and i've added the code in the worksheet and saved it as a excel macro-enabled workbook. But when i try to enter the formula i get the messege:"The formula you typed contains an error" I'm using Excel 2010, any idea what i'm doing wrong?

    1. Hi Anders,

      Please let me know which formula are you using. Could you copy/paste it here, exactly as you are entering it in your spreadsheet, with all cell references?

  48. The straight count by colour worked perfectly thanks, once I'd read one of the queries and copied to a Word document first. Saves me all the pain I've had of having an extra column with a letter to match the colour and then sorting and counting from that. Cheers!

  49. Hey i love the code that counts the selected cells while using a conditional formatting. I just wanted to know is there a function that does the same thing. i have a file that constantly updates depending on the drop down selection. i want a function that counts the number of highlighted cells when the conditional format highlights them. Some type of count function that works with conditional formatting and updates based off of the drop down that i select. if you understand what i am saying.

    Thanks again for the help.

    1. Hey Joe!

      Currently we know only one way to do this - using a macro. Other ways are not possible because of Excel's limitations. But we are working on this...

  50. I'm running into an issue where both the =CountCellsByFontColor(F3:F12,F3)and the =CountCellsByFontColor(G3:G12,G3)formulas return ALL of the cells with conditional formatting (standard red/yellow/green)rather than the ones matching the reference cell.

    Any idea why this could be happening. Also, I may have missed where someone else pointed this out, but your code above does not copy/paste cleanly into VB, as the line breaks aren't recognized. Takes a bit of manual tweaking. Not sure if this is a limitation of your site or something that can be fixed.

    Great solution to a common problem (if I can get it to work anyway), by the way.

    1. Hello Dave,

      The CountCellsByFontColor function does not work with conditional formatting, it is purposed only for the cells colored manually. Please use this VBA code instead.

      As for the code formatting problem, most likely it is caused by a particular internet browser. Can you please specify which exactly browser you are using so that we can test on our side? And thanks a lot for your feedback!

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