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

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

    Compile Error:

    Expected: list separator or )

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  22. This is awesome. Thanks!

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

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

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

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

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

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

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

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

  31. Hello!
    Thanks for this amazing code!

  32. Hello, why diagonal border in M. Excel in conditional formatting not active, if possible review it and fix it and add it to conditional formatting
    thank you

  33. Thank you! The very best article related to this topic. Made my day.

  34. hi thanks for explaining this i am now a step further forward, however my sheet, i have a cell that changer color when past a certain date, when it does this i wish it to send an email to address listed on another cell (same row) with a pre set message.

    is there something i can add to this?

  35. I am trying to write a formula to calculate a golf handicap. I use a spreadsheet that records scores each week across a line for each player. I need to use only ten of twenty scores for each player. I want to use highlight colors to identify the ten scores to use and then add them across the line of scores as well as divide the total by 10 and subtract 36. all the info I can find is about totaling columns. Is there a way to add in a line?

  36. This is really helpful. Well explained.

  37. When I use the SumbyColor function, it does not prompt you to enter the different portions of the argument like with other functions that prompt you to put a comma which then shows you performing the other arguments.

  38. HI,
    This is an amazing code,but it always rounds up my numbers. Is there a way for me to have it so that it can sum by two decimal places? For Example, .05+1.2=1.25....

  39. Thank you!

  40. Thank you, this is exactly what I needed!

  41. What if I want to count cell colors with multiple range & criteria? I don't see that as an option here, and i'm not a coder. Would it be simple to add an IF function to this to accomplish this task?
    example: =CountCellsByColorIF($A$3:$A$12,"Yes",$B$3:$B$12,"Accepted",!A20)
    IF A=True & B=True, Then Count X Color

  42. Hi,
    I am using this function in a large table of data and I am sub-totalling by 11 colors. It works to begin with and is brilliant, but then the function keeps returning a N/A Value not available error. Do you know why? It seems unstable. I am in Excel 2016. Thanks a million. Darren

  43. Great code and helpful for work.

    I noticed that whenever I try to apply the VBA code to other workbooks (other tabs) in the same spreadsheet, I keep getting a #NAME? error. Now the error is applied to all my countcellsbycolor formulas/cells. Any help?

  44. Thank you very much it was really helpful. Thank you once again.

  45. I have a sheet with numbers scattered and colors.
    I want to count how many time a individual number appears with a particular color
    EG how many time the number one appears with a yellow background, or reg background
    How many times the nu
    mber 2 appears with a yellow background or red background

  46. Great bit of code, is there any way to count the number of cells with coloured text if a cell contains two or more different colours? i have a calendar on excel with events happening per day, if there are two or more events per day then the text is coloured differently within the same cell. I am looking for the number of events per colour.

  47. Once I save it all the figures they went to hash marks. What format do I save the counting cells as?

  48. Thank You so much. It works perfect. Thank you once again!

  49. Hello Svetlana and team.
    An excellent product with superb easy to follow descriptive documentation. Thanks and well done.

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