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

  1. The code did well for simply counting cells by color, but my needs are more complicated. I'd like to learn how you set up the table color coding in the Delivery Column. I'm trying to use a date entered to set a Due Date and color code it yellow after 15 days past due and red after 30, THEN count cells by color.

    1. James:
      Can you provide some samples?
      Also, what is the relationship between the Due Date and the date entered?

      1. Decommission Ticket Dt Past Due Date(=IF(ISBLANK(V3)=FALSE,(V3+45))
        FALSE
        05/01/2018 06/15/2018

        Generated due date should be 45 days after the ticket date.
        When the due date turns 15 days past due, it should turn yellow (Cell Value > =$Z$26+15), 30 turns red. False remains white. I'm using Conditional Formatting for this.

        So far, generating the dates is the easy part and I can singally affect a past due date with color but it all jacks up when I bring it down the column.+

  2. Good Morning each time I infill a cell with the color selected it does not continue to sum the total unless I click on the formulated cell and hot return. Am I missing something?

    1. hi,

      if you look just above the section on 'Sum by color and count by color across the entire workbook' then there is a warning about this (it's just how excel works so nothing wrong with the code)

  3. I love this formula, but I am struggling getting it to work in an IF function.

    If Column I = 8 (or G, or L) and Column B is highlighted yellow (or red or green) how many cells are highlighted yellow?

    Here is my current formula for I=8, B=yellow

    =if('RawData'!$I$1:$I$500=8,(countcellsbycolor('RawData'!$B$1:$B$500,Counts!G3)),"")

    Thank you!

    1. Hello, Margaret,

      Unfortunately, your task can be solved only by changing the work of the CountCellsByColor function in your VBA macro.
      Since we do not cover the programming area (VBA-related questions), we can't help you with this. I can advise you to look for the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry I can't assist you better.

  4. Hi,

    thanks for the guide. It does however have limitations which is a shame. In my case, I have an excel spreadsheet with conditional formatting, so if something is a year out of date for instance, the cell fill colour will go to red. However, whilst the colour is displayed, excel actually lists the no fill for the cell colour within the font ribbon.

    1. Hi,

      Ive now seen the conditional format section on the above article. It can be used to count numbers by running the script, but does not generate any summary within excell that can be used. Is there a way of doing this?

      Thanks

  5. Hello Team,

    I am looking solution of my problem. is there any vba which change subject date and body date automatic in outlook13. i used to send daily sales report on daily basis so i am looking any code which automatic change date to today()-1.

    1. Hello.

      Thank you for contacting us.

      We are always ready to help you, but we do not cover the programming area (VBA-related questions).

      You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry we can't assist you better.

  6. Quick question... Just wondering why this code was made so difficult and mucky... for example :

    Function CountColors(Check As Range, Base As Range) As Long

    For Each i In Check
    If Base.Cells(1, 1).Interior.Color = Check.Interior.Color Then: Amount = Amount + 1
    Next i

    CountColors = Amount

    End Function

    Works exactly the same way...

    1. Had that backwards ;)

      Function CountColors(Check As Range, Base As Range) As Long

      For Each i In Check
      If Check.Cells(1, 1).Interior.Color = Base.Interior.Color Then: Amount = Amount + 1
      Next i

      CountColors = Amount

      End Function

  7. I carefully followed the instructions in this post, but I cannot get the code to compile using Windows 10 Pro 64bit and Excel 2016. When I paste it into the VBA editor, many of the lines appear in red. When I try to run the code, I get a compile error that I cannot always clear.

    I can clear the lines that begin with Dim by removing the leading spaces, but others are more difficult or not possible for me to clear with what I know about VBA.

    Is this code still valid for the latest version of Excel 2016? Or, what am I doing wrong?

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

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

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

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

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

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

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

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

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

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

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

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

    Syntax error
    (Module1 48:0)

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

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

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

  23. Hi, have a problem with these programms, it just dont work on conditional formating...how can fix it to work fine?

    1. Hello, Hugo,

      Please specify the programs you are experiencing difficulties with. What steps do you follow to accomplish the task? What result do you expect to get? It would be really helpful if you could send us a small sample table with your data in Excel to support@ablebits.

  24. Hi,

    This code is not working along with Conditional Formatting.

    Pls suggest the alternate solution.

    BR//Deepak

  25. the below code gives error in the first row

    Function WbkCountCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    vWbkRes = 0
    For Each wshCurrent In Worksheets
    wshCurrent.Activate
    vWbkRes = vWbkRes + CountCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    WbkCountCellsByColor = vWbkRes
    End Function

    Function WbkSumCellsByColor(cellRefColor As Range)
    Dim vWbkRes
    Dim wshCurrent As Worksheet

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    vWbkRes = 0
    For Each wshCurrent In Worksheets
    wshCurrent.Activate
    vWbkRes = vWbkRes + SumCellsByColor(wshCurrent.UsedRange, cellRefColor)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    WbkSumCellsByColor = vWbkRes
    End Function

    1. Hello, Manssoor,

      Looks like you haven't added the functions CountCellsByColor and SumCellsByColor before adding the code above. Please add these functions from the code in step 3.

  26. Hi there,

    Thank you so much for the code that allows me to sum cells by colour.

    I was wondering - is there a way for me to alter the code to allow me to sum cells that are any colour OTHER THAN a specified colour?

    That is, if I have say, a worksheet that has cells of 6 different colours - red, blue, yellow, purple, green and orange - can I tell excel to sum all cells that are NOT YELLOW? I would like cells that aren't coloured to be included (i.e. for excel to sum all cells that are red, blue, purple, green, orange and without fill on the basis that they are all NOT YELLOW).

    Thank you for your time.

    1. For the record, simply putting "" ahead of the colour code in the formula does not appear to work.

      1. Sorry! "" should have read as

        1. Hello, Katherine,

          Please in the code where you find If indRefColor = cellCurrent.Interior.Color Then, replace "=" with "<>". Like If indRefColor <> cellCurrent.Interior.Color Then, If indRefColor <> cellCurrent.Font.Color Then

          Hope this helps.

  27. hi, it is very helpful VB but it has a little problem, when it is working if we change the cell color (which had had the color) it does not work until we click the sum cell hand press enter .

    1. Hello, Reza,

      This is the standard Excel behaviour. 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.

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

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

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

    1. Hi Cristhian,

      Yes, it works with named ranges.

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

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

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

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

  35. Hello.

    Great post - I have one problem though. When i use this code in my worksheet, it only works if i give my cells a color directly. If i use Coditional Formatting, on my cells, and use this to change the colors, it does not work. Is there anyway where i can use the color generated by Coditional Formatting, instead of using the generic color which would be white.

    Please Help me! :D

  36. Hello!
    Thanks for this amazing code! I copied and pasted it in, but when I try and use the formula it just gives me the following ...#NAME?

    Can you tell me where I'm going wrong?

    1. Hi Laura,

      Got the same problem. I ensured that I saved the file with macros enabled (xlsm format via File | Save As...) and then in the IDE (Alt+F11), I ensured that the functions were part of my workbook (they were originally in PERSONAL.XLSB and possibly not in my file.xlsm). Finally, I saved the file and was then able to use the functions. Hope this helps you.

      The functions take a while to process as little as 1024 fields in a row with 7 different colors but it works great!

      Marc

      1. I got the same problem as Laura. So I made sure the functions were in the IDE workbook then changed the settings in the Trust Center to enable all macros and saved the file as xlsm, closed and opened the file again. Still got the ...#NAME. What is happening?

  37. Hi!
    I love this function, you did a very good job coming up with it!

    I am having an issue though.. I am using the first code you posted to count the number of cells coloured a certain shade in a specific column. It works great, until I change a cell's colour and the number in the function cell doesn't change -- I need to go to that cell and run the code again to see the up-to-date sum.

    Is this what you mean by 'manually' ?
    Can I fix this?

    Thank you!

    1. Hi Elle!

      By default the code doesn't track coloring of new cells. If you need to automatically update the result as soon as you color another cell, please use the Count & Sum by Color tool:
      https://www.ablebits.com/excel-count-sum-color/index.php

      Alternatively, you can use the code posted in response to comment #44.

  38. The Code is useful but when I saved it as xlsm format and reopen it the next day the Calculation is gone. I would need to add in the code again and recalculate again.

    Please advise if it can be saved in the document itself?

    Thanks and regards,
    Rebecca

    1. Rebecca,
      When you open it up just press enable macro again and it recalulates

      Nige

  39. I can't get the macro to show when I press Alt+F8. I copied the code multiple times.

  40. Is there a way to count cells of a specific color and containing a specific text? Say the name Bill appears on a sheet 10 times and 5 of the cells have been manually colored red. Is there a way to count how many times cells containing the text "Bill" and background filled red?

    1. Hi Brian,

      Yes, this is possible now, we've updated the code of the functions. Please copy the updated code to your workbook and then enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):
      =SUM((A1:B13="Bill")*(GetCellColor(A1:B13)=GetCellColor(D1)))

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

      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.

    2. =COUNTIFS(A1:L326, A330, A1:L326, getcellcolor(A330))

      Tried this and got "0" when, in fact there were 2 that met the criteria.

      A330 is a text value of a person's name as well as being filled with the reference color. I have tried it with "text value of 330" and "=text value of A330".

  41. Hi!

    Thank you for this script! I had problems when I tried to use it on Excel 2010. Excel said that it isn't valid formula and it has some errors.

    But then I replaced "," --> ";" and it worked (like this: =SumCellsByColor(D2:D14;A17).

    1. Hi!

      Most likely this is because you have the List Separator set to ";" in your Windows Regional Settings, so you should use a semicolon in all of your Excel formulas.

      1. I think it would be quite beneficial to mention this comma vs. semicolon option in the tutorial. It would have saved me a lot of time.. Thanks for the functions anyway, now that I got them working by replacing "," with ";" :)

  42. Just to follow up on my previous comment. I had already found http://www.get-digital-help.com/2013/10/02/counting-conditionally-formatted-cells-vba/ which works great in their example. We have pretty complicated conditional formatting on multiple cells. If I choose more than a row or a column or have a cell in the range that does not have the conditional formatting, I get a #Value. I do not really understand why theirs works as a function, but in a limited fashion, and yours works all the time as a sub...

    1. Hello Shelli,

      I have not tested their code, but it looks like it works only with conditional formatting rules based on formulas that changed the cell's background color, if true. I am not sure it will work with color scales. This code should also be checked with regard to min/max/duplicates.

      Also, the code implies that each conditional formatting rule found in the selected range applies to ALL selected cells. If it is not so, then the code will return #Value or even throw some VBA execution error.

  43. So confused about the "How to count by color and sum cells colored using conditional formatting" needing to be a sub and not a function. Not sure I understand what the limitation is. I am still playing with it. Works perfectly as a sub but need to get the count in a cell.

    1. Hello Shelli,

      Regrettably, there is no easy way to implement this as a function. I mean the function that would correctly handle all possible variations of conditional formatting. This would require a great deal of VBA code, tedious debugging and testing against different Excel versions and various Office localizations. (That is why the other solution you've tried works in simple scenarios and does not work with complex conditional formatting).

      So, our Excel specialists have weighed the pros and cons and created the Count & Sum add-in, which caters for all possible scenarios and works correctly with all Excel versions and all kinds of conditional formatting. You can download the evaluation version and try it out on your data.

  44. Or better still - how can I build a sumif formula around coloured cells. For example =sumif(range, [CellColour], [sum_range])?

    1. You can use the SumCellsByColor function to sum the values of cells of a given color in the selected range. Or, are you looking for something different?

      1. Hi Svetlana,

        Yes, looking for something a bit different, the cells that contain the values I want to sum are in a different row to the coloured cells so would be looking to do a calculation similar to the sumif function

        Cheers

        1. Hi Wazza,

          Thank you for the clarification, I understand now. Regrettably, this is not possible because our functions do not support array formulas.

  45. This is genius. However, would vlookup and hlookup by cell colour be possible. For example, =vlookup([CellColour],table_array, column_index_num, [range_lookup])?

    1. Hello Wazza,

      You need to add only one function to your workbook – GetCellColor. Then add an additional column to your lookup table, this should be the first, i.e. left-most column, and copy the following formula to it: =GetCellColor(F2). F2 is the needed cell color.

      An then, you use this formula =vlookup(GetCellColor(A1).... where A1 is the color-coded cell.

  46. Thanks for the prompt response. It worked. Hopefully, the add in that you / Aex is developing will work in both scenarios. Keep up the good work guys. Thanks

    1. Hi Samir,

      Our new add-in, Count & Sum by Color, is finished and available for download. And yes, it works in both scenarios and in all versions of Excel!

  47. Thanks for the Article -
    The spreadsheet I have contains both manual and conditional formatting in the same column,to provide same color - Red background color in this case - now the code that is written addresses either one of the two cases - ie either you can do a manual count or count by conditional formatting; is there a code that combines both cases in one so it can work in my situation.
    Also, do you know if there is a way to subtotal that can provide accurate count if the spreadsheet is filtered based on a certain criteria;

    Thanks for checking.
    -Samir

    1. Hello Samir,

      The Conditional Formatting code also counts and sums cells colored manually. As for subtotal, sorry we do have any idea.

      1. Hi Svetlana, I found your article very helpful. I am also looking for a way to subtotal the count of cells by color for sheets that require filtering. Is there any update on this? Thanks in advance!

  48. Hi! I'm trying to count by color and i've added the code in the worksheet and saved it as a excel macro-enabled workbook. But when i try to enter the formula i get the messege:"The formula you typed contains an error" I'm using Excel 2010, any idea what i'm doing wrong?

    1. Hi Anders,

      Please let me know which formula are you using. Could you copy/paste it here, exactly as you are entering it in your spreadsheet, with all cell references?

  49. Hey i love the code that counts the selected cells while using a conditional formatting. I just wanted to know is there a function that does the same thing. i have a file that constantly updates depending on the drop down selection. i want a function that counts the number of highlighted cells when the conditional format highlights them. Some type of count function that works with conditional formatting and updates based off of the drop down that i select. if you understand what i am saying.

    Thanks again for the help.

    1. Hey Joe!

      Currently we know only one way to do this - using a macro. Other ways are not possible because of Excel's limitations. But we are working on this...

  50. I'm running into an issue where both the =CountCellsByFontColor(F3:F12,F3)and the =CountCellsByFontColor(G3:G12,G3)formulas return ALL of the cells with conditional formatting (standard red/yellow/green)rather than the ones matching the reference cell.

    Any idea why this could be happening. Also, I may have missed where someone else pointed this out, but your code above does not copy/paste cleanly into VB, as the line breaks aren't recognized. Takes a bit of manual tweaking. Not sure if this is a limitation of your site or something that can be fixed.

    Great solution to a common problem (if I can get it to work anyway), by the way.

    1. Hello Dave,

      The CountCellsByFontColor function does not work with conditional formatting, it is purposed only for the cells colored manually. Please use this VBA code instead.

      As for the code formatting problem, most likely it is caused by a particular internet browser. Can you please specify which exactly browser you are using so that we can test on our side? And thanks a lot for your feedback!

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)