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

  1. This code works great! Thank you!

    Is there a way to extend the code to allow the conditional color to be in another cell?
    Rather than the color being in the cell of the number to be added, to have another cell on the same row have the color to be checked and then the corresponding number on the same row would be added to the sum.
    Does that make sense?
    The function call would be something like this:
    SumCellsByAnotherColor(, , )
    The range added by "REF COLOR RANGE" would be a column where the cells have different colors. So, say, some of them are blue. I'd want all the numbers in "number range" to be added only if the corresponding "REF COLOR RANGE" cell was blue on the same row.
    Thanks!!!
    Matt

    1. Sorry, it didn't like my greater-than less-than symbols in my post.
      SumCellsByAnotherColor(numberRange, refColor, REF COLOR RANGE)

  2. I need to have red green amber indications on one my cell based on the yes no ,NA answer from 10 column result . What formula should i right for eg I need the result on cell A1. Value to be identitified is B1=yes ,C1=NA,D1=NA,E1=NA,F1=Pending ,G1=NA,H1=NA,I1=Yes,J1=YES. Could you please advice how should I write the formula.As you are aware if yes,NA and 1 no (pending) it should be amber ,if all are no then it should be red and If yes and NA it should be Green.Please advice at the earliest

  3. damet garm means thank you buddy

  4. if you did so plz notify me

  5. Sir,
    i need vba code and farmula if in column 1 for different list of name and column 2 for red color so how i cant count trough name in column 1 because there is no color
    plz help me
    Thank You

  6. Hi Svetlana

    The conditional formatting count / sum by colour has worked for me, thankyou very much

    Is there a way to have the sum total show in a cell on the worksheet?

    Regards
    Kimberley

  7. Hi the code for counting by coloured cells works perfectly. But only once. Each time I colour a new cell I have to re-enter the code. Is there any way I can get the code to update automatically. Please not I have tried the fix as described in response to comment 44.

    Please also note that I am VERY new to Macros and VBA

    Thanks

  8. Hello everyone,
    I added a little bit to the code of one of the functions, the CountCellsByColor function. I added an If, Then statement so that the function would ignore hidden rows. Here it is, in case it would be useful to another user.
    ___________________________________________________________________________

    Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long

    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.Color Then
    If cellCurrent.Rows.Hidden = False Then
    cntRes = cntRes + 1
    End If
    End If
    Next cellCurrent

    CountCellsByColor = cntRes
    End Function

  9. It's possible use the function with a named range?

    1. Hi Cristhian,

      Yes, it works with named ranges.

  10. Thanks for the wonderful code, really appreciate the beautiful code.

    Just wanted to know the method to select multiple columns of data to calculate sum by color.

    For example,
    I need to consider data of column J, L, and N.

    Hence please let me know the corrections to be done in the formula.
    Thanks.

  11. The =CountCellsByColor worked perfectly. Thanks very much!

  12. Every time I input the CountCellsByColor function I get a compile error "For Each control variable must be Variant or Object"

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

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

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

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

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

    Sincerely,
    Tom

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

  19. Thanks a Lot it helped me

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

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

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

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

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

  25. Works perfectly! Thank you!

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

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

  28. Thank you very much, this is great

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

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

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

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

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

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

  35. ALL sorted...thanks anyway!

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

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

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

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

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

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

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

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

  44. hello,
    It was very helpful,
    thank you

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