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

  1. I Have a doubt, if we can make an attendence with extra allowance sheet of workers and if absent days can charged a fine of $1 and multiplies the fine if absents occur in the same month as $2,$4 etc.

    1. Hello, Linto,

      For us to be able to help you, please send a small sample table with your data in Excel and include the result you need to get to support@ablebits.com. Thank you.

  2. this was so helpful :D thank you!

  3. Hi, I have a doubt!. If I will have a range for example "$A$1:$A$10", and:
    A1(color black), A2(color blue), A3, (color yellow), A4 (color white), A5 (color white), A6 (color yellow), A7 (color white), A8 (color white), A9 (color blue), A10 (color yellow).
    How I can add some cells of the same color with a macro, for example:
    A1 = SUM(A2,A9) , A3 = SUM(A4,A5) , A6 = SUM(A7,A8) , A9 = SUM(A10).

  4. The formula doesn't seem to work well with Google Spreadsheet. There are times that I simply have to refresh and voila, it will work just the way I need it to but now, I've tried several times and it just won't work.

    I used this formula.
    =COUNTA(valuesbyCellColor("color", range))

    1. Hi Emmy,

      This function is written for the desktop versions of Microsoft Excel, the code won't work with Google Spreadsheets.

  5. The formula doesn't seem to work well with Good Spreadsheet. There are times that I simply have to refresh and voila, it will work just the way I need it to but now, I've tried several times and it just won't work.

    I used this formula.
    =COUNTA(valuesbyCellColor("color", range))

  6. Very interesting segment on how to count by color and sum cells colored using conditional formatting. I'm also impressed with the great Q&A on this site.

    Here's my question: rather than design a macro that creates a message box with the count, how could I modify the code to enter the number of the count in a particular cell? For example, when using Excel 2010, if I have a row of data that contains three cells which are red due to conditional formatting, how can I modify the macro to always enter the count of these red cells into cell A8? Thanks in advance for your time!

  7. Outstanding functions. Thank you!

  8. Hi,
    the code is great it's very good work.
    but i have a small problem, how can i make it work on network.
    i have a worksheet and i need all computers in my office open it and find this macro working, when i try to open this worksheet in other computer i find the code be 'C:\Users\moataz.abdelrady\AppData\Roaming\Microsoft\AddIns\Count cells by color.xlam'!CountCellsByColor(G4:AJ4,AP4).
    thanks!!
    Moataz.

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

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

  11. damet garm means thank you buddy

  12. if you did so plz notify me

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

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

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

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

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

    1. Hi Cristhian,

      Yes, it works with named ranges.

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

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

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

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

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

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

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

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

    Sincerely,
    Tom

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

  27. Thanks a Lot it helped me

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

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

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

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

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

  33. Works perfectly! Thank you!

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

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

  36. Thank you very much, this is great

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

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

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

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

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

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

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

  44. Kudos to you all.

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

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

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

  47. ALL sorted...thanks anyway!

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

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

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

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