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

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

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

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

  4. now it is working thanks

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

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

  7. Excel by color coding

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

  9. Nice code. works great. Thank you!

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

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

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

  13. this was great, thank you!

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

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

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

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

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

  19. How do I count non-contiguous cells?

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

    Compile Error:

    Expected: list separator or )

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

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

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

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

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

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

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

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

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

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

  31. Hi I wanted to ask this.
    Im using 3 colours for scheduling purposes
    Colour Red - Whether we've been to location
    Colour Blue - Person A has been to location
    Colour Purple - Person B has been to location
    When I wanted to calculate a single colour, it works out fine.
    But if I wanted to combine both colours to be counted as one, I run into value error.
    Could someone help me with this?
    This is my original forumula calculating 1 colour
    =COUNTCellsByColor(AJ70:BL70,$A$1)

    This is what i attempted when trying to caculate both mine and my colleagues colour.
    =COUNTCellsByColor(AJ70:BL70,$A$4)+(AJ70:BL70,$A$3)

    Could someone help me on this?

  32. When I close and open the worksheet again, It doesn't work (#Name?)

    1. Have the same issue!
      Can anybody help with this?

  33. I am trying to save this as an addin. I declared all the functions public, but when i run the function, i always get #Value Error.

    Please help!!!

  34. I want to return the sum in a cell for the conditional formatting VBA. How do I do that?

  35. Can I upload a picture saved from a cross stitch program that puts whatever picture I want into a graph and have it be able to tell me how many of each different color within that upload graph. If not do you know of a program that will do this

  36. How can i give three different colours diagonally to selected multiple cells

  37. At first the function would not work. I realized I formatted the cell as "Text." Once I changed it to "General," the formula worked! Thanks so much!

  38. Assalamualaikum Wa Rahmatullah
    Thank you for such a nice post. Extremely Helpful
    Best Regards
    Jawad Hussain

  39. Hi is there a way to make the cell color range a criteria, instead of range?

  40. Love You, it's exactly what I wanted.

  41. This is awesome. Thanks!

  42. Is there a way I can sum amounts by color, but the colors are in one column and the amounts (without color) are in another column?

  43. Hi, Really It's very useful and color Counts, Sum by Color tool, yes, it can count cells by font or background-color Well explained. thanks a lot...

  44. Hi - this is super useful! However is there a formula I could use to sumif the cell colour matches and the font colour matches?

  45. I just want to say thank you for sharing you knowledge. It is very useful.

  46. i cant edit the file to include more colours?
    I am trying to create a file that has all world countries and depending on the maual coloring, it will tell you pending countries you dont have yet. I need 7 colours, with one variant of each (light red, dark red, light orange, dark orange etc)
    plz help.

  47. Thanks for the coding - cut my work effort in half! Is there any way that this can be implimented into a COUNTIF/COUNTIFS formula - Ive tried majority of things and im not getting any results.
    The plan is that I would like to find a number based on how often one of my numbers has been highlighted through a table of alot of other numbers. I.e - say that A1 has the number in highlighted but also further along the table there is another one highlighted at J16 i would like to get a result of that.
    ive tried
    =COUNTIFS(Sheet1!U36:ZZ50,"15",Sheet1!U36:ZZ50,CountCellsByColor(Sheet1!U36:ZZ50,Sheet1!A16))
    where am i going wrong? thankyou in advance!

    1. Following this it would probably benefit to know my motive - i would like to know the number of time A21 (a number) comes up in the table d36:zz50 and has been marked yellow.

      1. I need something similar did you get an answer.....?

  48. Dear Concern
    How to used your formula with one more condition suppose i have different product in one column and there cashflow in second column and if the cashflow are highlighted by different colour as deffer and advance payment now i want for a particular product what is advance and what is deferred from huge data i cant get the required result from your formulas

  49. Dear Concern
    How to used your formula with one more condition suppose i have different product in one column and there cashflow in second column and if the cashflow are highlighted by different colour as deffer and advance payment now i want for a particular product what is advance and what is deferred from huge data i cant get the required result from your formulas

    1. I am looking for a similar forumula - trying to use SUMIFS with the countcellsbycolour - did you find a solution?

  50. Hello!
    Thanks for this amazing code!

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