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 6. Total comments: 831

  1. Your examples above are either (1) sum or count by color, or (2) sum or count by font. They are great and helpful. But I am just wondering if there is a formula to perform [count by color + font] and [sum by color + font] ?

    Thanks

  2. Is there any way to make the cells auto-calculate when applicable cells are formatted?

    1. Hello!
      Formulas are automatically recalculated when the cell value changes, but not the format. Your problem can be solved with a VBA macro.

  3. It does wonders. But I am still not able to execute the sum part of it. The value keeps coming to 0. No problem with the count function though. I will be grateful for the help.

    1. Hi!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?

  4. How can the macro can be modified if I only want to count or sum the colored cell after filter the table? My apologies, still much a novice at VBA.

  5. You are a goddess! This is incredible. Thanks! I got all the functionality to get this done.

  6. Thanks so much from Pematang Siantar City Indonesia.

  7. Worked perfect

  8. I have a table that lists all shipments not yet invoiced. I have colored all the rows using conditional formatting. As an example, shipments that are overdue for invoicing are colored red, based on more than 5 days past the arrival date of the vessel. We have several managers responsible for different types of commodities, e.g. bulk oils and petroleum, frozen foods, chemicals, etc.
    Can the CountIfs count how many reds, blues, greens, and yellows by manager? Do you have a code that can do this for me? At the moment I am counting manually.
    I have tried =CountCellsByColor(DBN!$B$6:$B$1299,SUMMARY!$C$3) but this results in total blue cells, not how many blue cells a particular manager has.

      1. Is there any VBA code for counting colored celled which are conditionally formatted?? i did not find solution anywhere for latest excel versions

        1. Hi!
          Have you tried the ways described in this blog post? Why don't you want to use the VBA code that is suggested in the first paragraph of the article?

  9. LOVE this code - thanks so much!

    In my sheet I have dates in Row 7
    And data in the rows under it

    I have a start date in Cell E4, and a end date in column d starting at D8

    I want to only sumbycolour when the dates are equal to or inbetween the 2 specified dates

    So Start Date is 12/11/21
    End Date is 16/11/21

    The row with the data i'm counting has data in it for the whole year

    I feel like I need to include an IF statement - but getting lost - appreciate anyones help!

      1. Thanks that helped heaps.... so I now have the SUMIFS working, and have it written like this:

        =SUMIFS(F5:Z5,F4:Z4,">="&B1,F4:Z4,"<="&C5)

        Where I have dates in B1 and in C5

        Is there a way to incorporate this with the sumbycolor - maybe a sumbycolorifs?

        I want the above to only sum when cells are of a specified colour.

        Thanks again

        1. Hello!
          You want to sum by both date and cell color. But these two conditions can be applied using different functions. Unfortunately, you can only combine them if you add VBA code to the SUMBYCOLOR function.
          You can filter by dates on your table and then use the SUM BY COLOR tool. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

          1. Thanks again - unfortunately due to other restraints in the workbook / I don't have the ability to use filtering for this.

            Anyone have the knowledge / skill required to write the code required to make sum by date and cell colour work together? Would so appreciate it!

            1. Hi @Alexander Trifuntov - any chance you can help with the code for this?

              1. Hi!
                Unfortunately, we are not in the business of writing VBA code. However, the filter is a basic Excel tool. I don't really understand why you can't use it.

  10. Hi
    I tried to use the function but every time I am getting #name error. What should I do?

  11. Good morning,

    I've tried the macro but it keeps giving me zero as result both for sum and count :(
    HELP!

    Thx,
    Mara

  12. Amazing!

    This is exactly what I needed.

    Thank you for sharing :)

  13. Excellent article and it worked perfectly! Thank you!! Well done!

  14. Many thanks, very useful reference, worked like a charm.

    More power & best regards.

  15. This is great! The only problem I've having is that the cell color doesn't get counted unless I use the format painter tool to copy the color from the cell in the formula. It also isn't working for conditional formatted cell colors. Any help is appreciated!

  16. I am trying to collect data that is in certain color boxes on my spreadsheet to total up amounts of hours someone has done over a certain amount of time. I am pulling my hair out trying to find a solution. Can you please help before i go bald lol.

    1. My spreadsheet is a register so i need to add up different color boxes. I have 6 different colors for the aims that i need to add up. So with one color (blue) it is looking for how many hours that have completed over a 43 week programme. There is two things that i want to do. I want to add the amount of blue boxes there are but i also need to know the total of hours they have completed over this time.

      1. Hello!
        Pay attention to the following paragraph of the article above - How to count by color and sum by color in an Excel worksheet. What problems did you encounter while using this instruction?

        1. A friend of mine managed to do it in excel but when doing it on google drive sheet its not working

          =ColorFunction(K5,C14:V56,TRUE) this is the one she used.

          It keeps coming up with error unknown function

          1. Hello!
            These guidelines apply only to Excel. If you need advice on Google Sheets, search for an answer or ask a question in the related section on our blog. Try it here.

  17. I have an Excel sheet that I am trying to use as a temporary scheduling tool. It is set up to look like a gantt chart with the colors set with conditional formatting based on how long each step in the scheduled process will take to accomplish. I'm trying to figure out how to count the times each conditionally formatted color shows up in a weekly bucket to calculate my labor needs. Is there a way to amend your VBA to read just a portion of my table? Or any ideas on how to accomplish this?

  18. Thank you so much for the count by color and sum cells colored using conditional formatting. It's my problem but simple it has an error in something. When I format some spreadsheets with conditional to color. Then use the formula CountCellsByColor(range, color code). The result is wrong. So I hope have good news about it

  19. It works but it only does one row. When I try to copy the formula down it doesn't work. HELP!

  20. Thank you so much, it was really helpful!

  21. I was trying few times failed until found your tips, it is working well, thank you so much for your sharing

  22. I have been helping my wife with some spreadsheets. Everything was going great until she needed to count employees and the new new employees were typed in red font and were not counted in the total number of employees. Your CountCellsByFontColor worked perfect.

    Thank you very much!!!

  23. This was awesome and so easy to implement. Thank you so much!!!

  24. now it is working thanks

  25. i need to count multi color in big range contains of about 1000 cells could you help please

  26. I tried the countcellsbycolor formula, it just plain did not work! i followed all instructions right & got the #NAME? dumb error over 7 over again.

    1. I got the same result, pressed F9, and it worked.

  27. Excel by color coding

  28. Thank You so much. This is really useful.

  29. Nice code. works great. Thank you!

  30. In succession to my former comment on 7th november:

    The solution to this is disabling automatic calculation in excel this can be done in "Formulas" -> Calculalation Options -> Manual...

    Now when you want the VBA to calculate just hit "Calculate Now".

    This does the trick for me...

    Works like a charm

  31. Thanks alot for putting this together. I only read a couple of comments with the same issue that I have.

    I added the code correctly and everythigns is working fine!

    The problem is just that every cell action that I do, now takes a lot of time. (around 40 seconds) not only the ones where I have entered the forumla (in total 5) but every other cell that I create, delete, etc. that has nothing to do with the sumbycolour...

    And also the sumbycolor takes around 40 seconds for each formula...

    is that normal?

    I have a 2015 MacBook with 4 GB of RAM and i5 CPU. I guess that should suffice, shouldn't it?

    I have four sumbycolour cells, each is specified with the column to look for, e.g. "=SumCellsByColour(F:F,A10)"

    Pls help, otherwise I am forced to look for another solution, although your code is otherwise perfect for me.

  32. " Ageing Cat
    Demand Date " 1-Nov to 10- Nov 11-Nov to 20-Nov 21-Nov to 30-Nov
    91 - 180 Days 1,00,000 20,000 25,000
    181 - 270 Days 25,000 5,000 10,000
    271 - 365 Days 1,00,000 20,000 25,000
    More than 365 Days 25,000 5,000 10,000

    1. In excel AA Column as a cell with color and another AS column weeks

      how to sum the amount with a color receive again which week.

  33. this was great, thank you!

  34. I wonder could you write a macro/code to sum and count cells in one column based off the conditional cell color of another column AND the number code of 3rd column please? It would be something like:
    CCount(IF(CColor(B1,A4:A100)),IF(C4:C100,=1),(B4:B100))
    ConditionalCount(IF(ConditionalColor(ColoredCell,RangeOfColoredCells)),IF(RangeOfNumberCodeCells,ValueToEqual),(RangeToBeSummedIfConditionsMet).
    and could use =, for the number value.
    Thank you for your time ;)
    If anyone knows of a macro like this please reply Thanks.

  35. Thanks so much (again), you guys are the best. Such a resourceful site, full of top tips and examples.

  36. I have an issue I just ran into with this that was hoping to get some help on. When the cells are being colored by rules in the column, the CountCellsByColor doesn't count those. I have a reference cell that is colored the same as those in the column that I'm using. I'm just using the simple =CountCellsByColor(AE4:AE24,C25) where C25 is colored the same as some cells in AE4:AE24, but cells in that range are not manually colored, they are being determined based on rules. Any ideas?

  37. The VBA works great, thank you for posting. How do I incorporate the GetCellColor to count cells between a date range using a =CountIfs function? I have done the following so far but its returning a zero value:

    =COUNTIFS(E7:NE7, GetCellColor(NK4),E2:NE2,">="&E2,E2:NE2,"<="&TODAY())

    **E7:NE7 Data Range
    **NK4 cell color reference
    **E2 Start Date
    **NE2 End Date

    Appreciate the feedback. Thanks.

    1. Hello Jonathan!
      I cannot validate the formula on your data. But something like this will work for you.
      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((GetCellColor(E7:NE7)=GetCellColor($NK$4)) * (E2:NE2>=$E$2) * (E2:NE2<=TODAY()))

      Hope this is what you need.

  38. This VBA code works great, thank you for posting it. But, since my cells are conditionally colored green for the lowest sum in a range, it will not pick up on the conditional color. Any way you happen to have VBA code for conditionally colored cells?

  39. How do I count non-contiguous cells?

  40. I keep getting an error in VBA that says:

    Compile Error:

    Expected: list separator or )

  41. i had to count colored cells in a ROW and the formula worked great, very flexible for non-vba coders! Many thanks

  42. This was a great help. Thanks so much!

  43. I have copied one of your Function formula (CountCellsByColor) and made small changes to it (see below). But when I use the formula =CountCellsByColor(A16:G16,J2) it gives me #VALUE!
    (A value used in the formula is of the wrong data type). What am I doing wrong?

    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).DisplayFormat.Interior.Color
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.DisplayFormat.Interior.Color Then
    cntRes = cntRes + 1
    End If
    Next cellCurrent
    CountCellsByColor = cntRes
    End Function

  44. HI,

    I am using the conditional color code counting macro as below.
    In excel 2019 I get an error "Compile error: Invalid outside procedure", can you help me to correct this error?

    Thx
    Ton

    Sub SumCountByConditionalFormat()
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
    Dim sumRes
    Dim cntCells As Long
    Dim indCurCell As Long

    cntRes = 0
    sumRes = 0

    cntCells = Selection.CountLarge
    indRefColor = ActiveCell.DisplayFormat.Interior.Color

    For indCurCell = 1 To (cntCells - 1)
    If indRefColor = Selection(indCurCell).DisplayFormat.Interior.Color Then
    cntRes = cntRes + 1
    sumRes = WorksheetFunction.Sum(Selection(indCurCell), sumRes)
    End If
    Next
    MsgBox "Count=" & cntRes & vbCrLf & "Sum= " & sumRes & vbCrLf & vbCrLf & _
    "Color=" & Left("000000", 6 - Len(Hex(indRefColor))) & _
    Hex(indRefColor) & vbCrLf, , "Count & Sum by Conditional Format color"
    End Sub

  45. Thanks so much for this! Saved me much time and effort!

  46. good article, However it is not working for me.
    I have a my data, I have applied a conditional formatting where any cell in that column had a value of "y" the entire row will be colored green.
    Right now i have 20 rows of data, about 11 of them have "y" in Column D. i have copied the code to VBA module, but my result for color green cells is 20, not 11.
    Not sure why is not working.
    Help, please

  47. Thank you so much! That worked like magic!

  48. The code is great and it saves me a lot of time in working with the color-code count and sum. Thanks for the generous gem, as other said.

  49. Is there a way to know how many cells I have by both the color of the cell and the font of the cell. Like if I had cells that were blue with black font and then cells that are pink with black font.

  50. Should I be able to install this module to my personal workbook so that I can use it for any excel spreadsheet? If so, what am I missing to get that to actually work? Since I'm trying and failing at that.

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