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

  1. I got this to work perfectly on another spreadsheet. Now I'm trying to use it again and I'm getting an error.

    It says "Compile error: Sub or Function not defined."

    I'd appreciate any help.

    Thanks,
    Mike

    1. I have the same problem. See my post 236 below. I am beginning to think the code does not work with the latest version of Excel 2016.

    2. Sorry, I'm using the WbkSumCellsByColor function.

  2. Hi, can you tell me how to use the CountCellsByColor formula if the range isn't adjacent cells. For example, I want to count cells if a certain colour where the range is A1, D1, G1 rather than A1:G1?

    Thanks.

  3. Thank you! its really a bbbiiiiiigggg help! Works fine with me :)

  4. One simple update that might be useful in the original code is to skip over hidden cells, like those hidden by a filter. Here's what I did to CountCellsByColor:

    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 And cellCurrent.Rows.Hidden = False Then cntRes = cntRes + 1
    Next cellCurrent
    CountCellsByColor = cntRes

    End Function

  5. Thank you for a great post. I was able to implement for a colleague in 5 mins (2007). Use of "key" or reference cell is great - no need to find the color code. Well done!

  6. I have set up an excel sheet tracking the performance of kids in sport. The columns in question return a result of the test in question. Based on the result, it will be colored, green, yellow, brown or red.

    I copied the text you recommended and put it into the Macros window as instructed. I then tried counting how many cells returning numbers with the different colors mentioned above, using the "CountCellsByFontColor" but I couldn't get it to work, regardless whether the text was bold or otherwise.

    can you help me with this

  7. Thanks mate :) this is very useful and helpful

  8. Count for colors works super slow but perfectly! Thank you so much.

  9. I tried your VBA code for counting conditionally formatted cells. I need this code to be a worksheet function. I have over 80 columns of data to apply the code to. It is not practical to select range and reference cell, the press Alt+F8 80 times.

  10. I cannot get the workbook sum function to work

  11. To count cells by colour on the same worksheet, Microsoft recommends a macro based on just 10 lines of code, compared with you umpteen lines. Is there a special reason to your code?

    1. I just noticed that the MS code just does a count, whereas your code sums as well. Apologies for my misunderstanding.

  12. Thanks for the great article! I am working on a duel scoring system where each has a numerical score (1-5) and a traffic light score (by filling in the cell colour with red, Amber or green) the two scores are not related. Is it possible to modify the code so that it calculates the average score and fills in the cell colour according to the agreement of the traffic light score (all green = green, all red = red, anything else = Amber)?

  13. Hi,

    Great stuff.. have learnt a lot for your website.

    Need one help regarding sum of cells based on color formatting. The code provided is macro, how to convert this to a function so that I can use it as and when needed

    Thanks,
    Prasad

  14. i can not able to see color and sum by color in an Excel worksheet.so i need help from you.please explain it brief

  15. Thank you, worked great

  16. I've seen the question I have a few times but there is never an answer associated. I run reports for my boss and some of them require totaling by sum and count for programs during the month based on conditional formatting. The formal works in that I can see the count and sum but, a) I need to include the numbers in my report and b) the count and sums are in separate boxes. Is there a way to populate the answer into a cell with the needed count or sum based on conditional formatting rather than a pop up box which doesn't work for my boss when I print the report.

  17. Im using Excel 2013. What is the formula to count the cell color of specific information within the cell. EXAMPLE: I have APPLES(red), GRAPES(green) and STRAWBERRIES (red). I want to return the count of cells colored red that are STRAWBERRIES only, not all cells colored red.

    1. Hi Carl, I'm needing the exact same info! I'd like to count by cell color, IF a specific text criteria is met (just like you said, Cell is RED and contains text "STRAWBERRIES"). I do a lot with data, and automating things like this would be a huge help. I've tried combining formulas and although they don't error out, I'm not achieving the response I need. Does anyone know if there is a solution?

  18. Hi,

    I'm using the color count for conditional formatting Excel 2010. How can you have the count be placed in a cell automatically after entering data instead of having to run the rule and only getting the result in a pop-up box?

  19. Hi,

    Your example is good one.

    I am in the same problem, but what about merged cell. How do I count merged cell fill with specific color?

    regardsd,

    Santosh

  20. I followed the instructions verbatim for counting cells that contain a color and it didn't work. When I try to use the formula, Excel returns #NAME?
    I did save the spreadsheet as a macro-enabled spreadsheet. I even went back into the VBA editor to make sure the code was still there and it was. (and I had copied the code verbatim by selecting it from this website and pasting it.)

  21. hi,

    how to count the date between one week or two weeks.

    for example in cell A1 is date. I want to count the date after one week( between 2 to 7weeks).what is the formula I can apply. I got the formula to count for today's date or overdue date countifs(a1:a10,"<=&today(),b1:b10,d15), but I cannot get the formula to count the date in between one week.
    1/8/16
    2/8/16
    3/8/16
    8/8/16
    11/8/16
    12/8/16
    15/8/16
    17/8/16

  22. This is a fantastic tip !

    Thankyou so much :)

  23. Hi Please help me with how i can use indirect formula in excel 2010 because when i m trying enter formula i am getting following error #REF!
    I am entering formula indirect(select cells) but i am not getting when i am entering formula like this =indirect("Select") i am getting the outcome but not able to use for multiple cell and sheets need your help to get the indirect formula in excel 2010

  24. This macro for "How to count by color and sum cells colored using conditional formatting" is a big help. Well done. I was wondering if there is a way to take the output that pops up in the Message Box and have that info put into a cell. For instance, the Count is what I am really looking for. Is there a way to have the Count displayed in a cell for the range selected instead of a popup window?

  25. You guys are awesome !! It works :)

  26. Hi,
    Am running Macro with cells that have both hyperlinks to server as well as conditional formatting of them cells. Have copied the codes from above script (CountCellsByColor in addition to the Conditional Formatting code later in the page) and they fail to capture the cells which are conditionally formatted, returning a result of '0'.
    Moving the data selection area outwith the hyperlink/formatted area returns valid sum data just cant seem to get the Conditionally Formatted cells to be recognised. Ground to a halt here, help...

  27. Please ignore my previous post I finally figured out my blunders.

  28. I see that the code works for everybody but me, so it must be me, but would still like know why it doesn't work. The error on the worksheet in cell F2 is #Name?. I first hand copied from the web site the code, then hand typed it in, still the same error. Then in cell F2 , I hand typed the formula, that was said to type. =CountCellsByColor(A1:A10,E2), where E2 is the cell that has the color I'am trying to count. I work on Excel 2010, and Windows 7. Looking for how many yellow cells there are.

    1. Steve Martin, you are not the only one who is having the problem. I am glad you figured it out. Could you please share what was wrong. I am working in Microsoft Excel for Mac v15.28. I entered pasted the code into the vbe, then added the formula as was specified, and I also received the #NAME? reply in the box with the formula. I have no idea what I did wrong. any help would be greatly appreciated.

      1. If you are saving the macro in your personal workbook, the name of the function will be PERSONAL.XLSB!CountCellsByColor, so you will get a #NAME error if you just put CountCellsByColor (if you save it just to the workbook, this name should work just fine)

  29. Thanks for sharing, I have certain merged cells to count in between the range. is there a solution yet for that

  30. Great Stuff. Thanks for sharing.

  31. Thanks for the code .. it works great !!

  32. Hi,

    Just wanted to say thanks so much for sharing this awesome code.

    Excel is already an amazing tool and people like you just make it better.

    Thanks,
    Brett

  33. Hi
    When I use the macro, it works fine. However, when I enter another value with font color. It doesn't refresh the sum values. Please help.

    1. Hi Gregg,

      This is the standard Excel behavior. It doesn't see the change in colour as change in data. Recalculation will work if data will be changed in at least one cell in the calculated range or if you run Formulas -> Calculate Now.

  34. Need function for gettting color code of conditional fomatted cell instead of macro running and providing the count and color code.want to use that color code in if statement along with gettting color code of conditional fomatted cellfunction to perform it with next function like vlookup or some other...

    Please I think you can do it because in macro you are able to generate the color code just please provide some function to get conditional formatted cell color....

  35. Amazing! I've tried and it works great . thank u ^^

  36. How do I count SEVERAL colors as a total sum e.g. red AND blue together without first having to add these ranges to independent cells and then count the total sum of these cells using the SUM function?

    Specifically, I have several columns where the colors specify the ranges of time periods, so say, between 8 AM to 6 PM I have a light green, but past 6 PM to 11 PM I have a dark green. I want to add these two color values together in the cell at the top of the column in order to give me the total sum of colored cells in that column. I have tried to nest the code suggested on this site but it keeps telling me that it doesn't work so how do I do it? I need to nest up to 8 different colors in one function, ideally, so I can copy it across all relevant columns without having to change the content of the function, would I change the colors of the columns in the future.

    1. Hi Samuel,

      You can combine several SumCellsByColor functions in one formula.
      For example, the following formula sums the cells in range A1:A10 with colors from A1 and A2.
      =SumCellsByColor(A1:A10, A1) + SumCellsByColor(A1:A10, A2)

  37. Thank you for the information you provided which is very helpful. Can you tell me how to count cells, by color only, that may have a number in each cell? Once I insert the countcellsbycolor formula into a cell along with the color cell, and in this case, it's a calendar month where certain dates are color coded, I keep getting this error message #VALUE! which is also totaling up the sum of the "date numbers" within the color coded cells. I just want to count the cells that are color coded in a particular month and not total up the numbers within those cells. Thank you.

    1. Hi Pat,

      To help you better, we need a sample table with your data in Excel and the result you want to get. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  38. =CountCellsByColor(F2:F14,A17)

    Using the code above I get "Compile error: Invalid outside procedure" and i even copy the code & steps above above exactly...any ideas? Thank you!

    1. Hi William,
      This error means the code structure is broken.
      Please try to remove all the code and copy it again.

  39. hi there...when i save the file as a micro enabled xls file, the formula returns a #name? error....

    Thoughts? Please help

    Thanks

  40. I have a dynamically updated calendar I created without VBA where the formula finds the last column that there is data present up to the current month and displays said data. However, the data is color coded and i was wondering if there is a way to only display cells with a certain fill color or just to exclude cells with a certain fill color

  41. Hello and thank you for this great formula!

    I have 1 thing I cant seem to figure out.

    I am using the formula from answer in question 184, but I can't seem to use it to count cells with any text (*)

    I have tried this;
    =SUM(('1sthalf'!X6:X23="*")*(GetCellColor('1sthalf'!X6:X23)=GetCellColor(A1)))

    The above returns 0 as the value, there is 1 cell with text, so answer should be 1.

    Meaning I want it to count all white fields (A1 is white) where there is text in cells X6:X23. It works once I just write "tr" which is the only cell with text, but sometimes it is many different combinations of text in the cells.

    I hope I dont have to copy this formula for every different text string I have in my range? I have approximately 100 different text strings, so I doubt excel has room for such a big formula.

    Thanks!

    1. Hello Nicolai,

      Please try to use the following formula instead:
      =SUM(('1sthalf'!X6:X23<>"")*(GetCellColor('1sthalf'!X6:X23)=GetCellColor(A1)))
      The asterisk character works only for the search.

  42. Hi Rohit,

    Covert the data range to a Table.[Ctrl+T]function.
    Highlight the column[s] you want to sum/average/mean etc and set the Conditional formatting.
    In the column header of the table you can use the filter to total by selecting the colour condition you have set.

    1. Can you please write out the code? I'm new to VBA.

      Thanks in advance

  43. When i am using the GetCellColor function by passing the cell number, no matter whatever the background color of the cell is, it is always returning the value 16777215.
    Could you kindly help?

    1. I was able to figure out the problem. I have conditional formatting rules set up on the cells, which gives an impression that the cell has some background color, however in reality there is no color in the background. This function is picking the default cell color in the background instead of the one from conditional formatting. If i use this function on any other cell without having conditional formatting, it returns the correct value.
      Is there way to use this function to pick up conditional formatting color?

  44. Hi,

    I can't seem to get the countcellsbycolor function to work, do to the comma(,) in the formula. did i miss something?

    1. Hi Alex,

      Most likely you have a custom locale in Excel that uses a different delimiter for separating values in the formula. Please go to Control Panel -> Region and Language-> Additional settings and replace the delimiter to get the formula to work. If you still encounter any issues, please send your spreadsheet to support@ablebits.com, we’ll take a look at it.

  45. when i use this code its gives me a error
    Compile error:

    Syntax error
    (Module1 48:0)

  46. Great job Svetlana on this "How to count and sum cells by color in Excel 2010 and 2013"

  47. Hello mam,
    i want to count quantity of particular items,its created by different codes like ABC,ADB,ACD....etc. Now i want to count a particular item means ABC=qty ?.
    I used =countif(D4:D52,"ABC") but it counts only no of codes in sheet,i want to count qty of ABC its mentioned in other coloumn and ABC code is repeating below raws. i want to calculate the qty of ABC,ADB,ACD...etc
    How it possible ???

    1. Hello Robin,

      You can use the SUMIF function like:
      =SUMIF(D4:D52,"ABC", B4:B52)

      Where column D contains your ABC, ADB, ACD, etc, code, and column B is the quantity to sum.

    2. Change Room CFL2 CFL 2Pin 11 Watts X 2 nos 1
      Liquid manufacturing PL3 PL Type3 36Watts X 3 nos 6
      Liquid Filling PL3 PL Type3 36Watts X 3 nos 2
      Liquid Material Entry CFL2 CFL 2Pin 11 Watts X 2 nos 1
      Liquid Washing PL2 PL Type2 36Watts X 2 nos 1
      Liquid Bottle Wash TR2 Tube Rod 2ft 14 watts X 2 nos. 1
      Liquid Packing TR2 Tube Rod 2ft 14 watts X 2 nos. 8
      Sugar Storage PL2 PL Type2 36Watts X 2 nos 5
      Carton Coding PL2 PL Type2 36Watts X 2 nos 1
      Production Entrance CFL2 CFL 2Pin 11 Watts X 2 nos 1

  48. Hey Martin, would be great if you could help me for above query....

  49. your functions are quite good to use, Is there anything where I can count basis on a given condition in cell i.e 2015 (with green color), note- there are other cell as well with 2015 red color, but I need the count for 2015 with green cell. please help
    Regards
    Mahesh

    1. Hi Mahesh,

      Sorry for the delay,

      Please copy the VBA code to your workbook and then enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM((A1:B13="2015")*(GetCellColor(A1:B13)=GetCellColor(D1)))

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

      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.

      1. HI Alex,
        I am trying to count the number of time a cell is red and has the number 33 in it. When I try to create the array you posted on Feb 11, 2016 the value I receive in the cell is 0, when the answer should be 2. I am using =SUM((C2:C132=G13)*(getcellcolor(C1:C132)=getcellcolor(G4)))
        Thanks for the help!

        1. Hello Phyllis!
          In your formula =SUM((C2:C132=G13)*(getcellcolor(C1:C132)=getcellcolor(G4)))
          instead of C1 use C2
          =SUM((C2:C132=G13) * (getcellcolor(C2:C132)=getcellcolor(G4)))
          I hope this will help, otherwise please do not hesitate to contact me anytime.

    2. Sounds about like what I need. Have you gotten any response yet?

        1. Thank you very much for your kind words, Lisa. Though we can't guarantee a quick reply due to a big volume of comments we get, feel free to ask any questions you have if anything remains unclear.

  50. Hi, I have tried to use your formula with a SUMIF formula. I have two columns; column A which is a text entry cell (project name) and column B which is a currency cell. I want a formula where I can add the sum of the cells in column B but only if the text colour in Column A is green. I tried using the formula:

    =SUMIF(A5:A12,"SumCellsByColor(A5:A12,A19)", B5:B12)

    where A19 says "Total Green Projects" in a green font. Whilst you might wonder why I don't just change the text colour in the B column and use your regular formula, in reality the spread sheet is far more complex and column B is actually many columns away from column A (and usually in a variety of different colours). If you could help in any way that would be really appreciated!

    1. Hello, Martin,

      Thank you for contacting and for sending us your sample workbook. We have just replied to you by email, please check your inbox.

      1. Thank you for the spread sheet however it doesn't work when I change the colour of the text of the cells in Column A; the change in value is not reflected in the formula.

        I also see that if I insert a new row, the formula shows "VALUE!".

        I want to be able to change the colour of any cells in column A (to green, or any other colour) and for the corresponding increase/decrease in the total sum of column B, which is displayed in the formula cell. As new projects arrive, I want to be able to insert new rows and have this included in the formula.
        As this is a very fluid spread sheet which is always changing, the formula doesn't seem to work with any changes. Is this something that can be fixed? Is this possible?

        1. Hi Martin,

          We apologize for the delay in our reply, we get so many comments we’re having a hard time answering them all. Practically anything is possible :) Could you please send the spreadsheet with the formula you inserted to support@ablebits.com? It is very likely that you need to fix the beginning of the range with the help of absolute references, while keeping the end of the range relative.

          There is one peculiarity to consider: the Count/Sum formula is not re-calculated automatically if you set the cell color using a ribbon button in Excel, i.e. the font and fill color icons on the Home tab. However, there is a way to go around this issue, please see the following comment:
          https://www.ablebits.com/office-addins-blog/count-sum-by-color-excel/#comment-52201

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