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

  1. I have a very large spreadsheet with several columns, see example below. Each month I need to count active Companies(color coded by location) for example; in January column, only count Companies that are blue and active, in February column only those that are blue and active, etc. Then repeat for Companies coded purple.

    I was able to use your code above to add by color, but now I need to eliminate those that have terminated.

    Company Group # Eff Date Term Date January February March April
    ---------------------------------------------------------------------------
    ABC Co. 123 1/1/10 1/1/14 $0 $0 $0 $0
    123 Co. 456 4/1/09 $20 $20 $20 $20
    D's Bar 789 2/1/07 2/1/14 $15 $0 $0 $0
    PJ's Pub 654 1/1/14 $45 $45 $45 $45
    Pizza Pub 321 1/1/13 $10 $10 $10 $10
    Day Center 852 1/1/12 3/1/14 $25 $25 $0 $0

    ABC, 123, D's Bar are Blue - therefore result in January s/b 2, February s/b 1, March s/b 1.

    Pj's Pub, Pizza Pub, Day Center are purple - therefore result in Jan s/b 3, Feb s/b 3, March s/b 2.

    Is there a way in January to countif blue and term date is after 12/31/13? Then in February countif blue and term date is after to 1/31/14?

    I need to read by date rather than $0 as sometimes commission is received even though terminated.

    Thank you in advance for any help you can provide, as I am very new at this.

    1. Hello Wendy,

      Yes it is possible. Please send us a sample workbook at alex@ablebits.com, and we will work out the solution.

      1. I have a similar situation; is there any way you can publish and example of how to do this?

        In my case, the sheet isn't very big, I'm just trying to automate things as much as possible so that data can be updated when I'm not there to adjust things. I need to be able to sum all cells of a certain color that are adjacent to cells containing specific text.

        (I have a series of quantities in column A, and quarters - Q1 2016, Q2 2016, etc. - in column B. I need to sum only those quantities that occur next to a specific quarter, and are of a specific color.)

  2. Good Morning,

    Is there a way to count by background color only if it is prior to a specific date?

  3. When it brings me back to the module I inserted it highlights the very first GetCellColor in the module.

    Thanks again!

    1. Hi Janis,

      Please make sure that you pasted the complete code in the module. The compile error usually indicates that some part of the code is missing. Please verify that the code starts with "Sub" and ends with " End sub". You can also download this sample workbook with the CountCellsByColor and SumtCellsByColor functions ready for use and try them on your data.

  4. Hi

    I posted the 3 modules you provided in my workbook. I saved it as a .xlsm file type. When I try to enter the formula - GetCellColor or CoutCellsBy Color - it brings me back to my modules coded with the error:

    Compile error:
    Expected:end of statement

    I don't know how to fix this.

    Thanks for the help!

  5. This code works great....I could use one additional function. I need to check and see if a cell has data in it before I count it.

    1. Thank you Dennis, please let me know if I can help in any way.

  6. The result I get adds the totals of all the cells in the row that are being conditionally formatted. I have 3 different conditional formats, I understood that the count of sum function would only act on those that met the format of the cell I wanted summed or counted. What am I doing incorrectly or does the formula not act on the specific formatting of the cells in question? I can send the file to you if you'd like to view it. Thank you.

    1. Hi Jerry,
      The =CountCellsByColor() and =SumCellsByColor functions count only cells of a specified color. For example, =CountCellsByColor(F2:F14,A17) will color the cells in the range F2:F14 that are of the same color as cell A17. If it does not work in this way for you, please do send us your workbook at support@ablebits.com.

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

  8. The straight count by colour worked perfectly thanks, once I'd read one of the queries and copied to a Word document first. Saves me all the pain I've had of having an extra column with a letter to match the colour and then sorting and counting from that. Cheers!

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

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

  11. Hi,

    I am sure this is a questions for a different topic but hoping you can help? I have a list of 20 individuals and I want to create a 4 quadrant pie chart and place them in the region their scores qualify them to be in the pie. Can this be done?

    Thanks,
    Tatiana

    1. Hi Tatiana,

      You can do this via a pivot chart or CountIfs function. This sample workbook demonstrates both solutions. Hopefully, this is what you are looking for.

      1. How can i plot the Name within the chart?

        1. Sorry, I do not know a way. You can ask this question on excelforum.com or mrexcel.com forums.

  12. maybe I cannot accomplish what I want -

    1. I have a column of numbers (cell A5 - A434) some of those cells w/in that range are colored yellow.

    2. on line A435 - I want to sum ONLY the yellow cells.

    Is your tutorial "How to count by color and sum by color in an Excel worksheet"
    the correct one to utilize?
    if so, I am having trouble with your code (when I copy/paste) - it copies red and utilizes 2 lines.

    I have copied/pasted other codes and they appear to paste as is.

    Thank you for your guidance
    Angel

    1. Is it possible to SUM the amounts in a colored cell? Not how many of a particular color but for example - I have 7 yellow cells - each has different amounts in there... I want an end result.

      I was able to finally get my other problem resolved so I thank you dearly!

      1. Hi Angel,

        Please use the SumCellsByColor function instead. It does exactly what you need - sums the values of colored cells.

    2. Hi Angel,

      Yes, this script can help you to achieve the desired result. Try copying the code to Notepad first and then copy/paste from Notepad to Excel.
      Alternatively, you can copy it directly form the blog in some other browser.

      If this does not work anyway, please send your sample workbook at alexander.frolov@ablebits.com and we will figure this out.

      1. Hi Svetlana,
        I got it to work. Thank you. I figured out that you did not need to run the code, just save it and then continue on with the formula.
        Thanks

  13. Hello,
    I have tried to cut and paste the code from your tutorial
    How to count, sum and filter cells by color in Excel 2010 and 2013.
    When I do, it shows up red in the module and utilizes 2 lines
    it does not appear like yours.

    I have cut / pasted other codes and they worked but this one doesn't. Can you tell me if Im missing something on this one?

  14. Hi Svetlana,

    I am using this this functions and its great "How to count by color and sum cells colored using conditional formatting", but i need to go an extra step and actually publish the count in a designated cell. How can i do this?

    Thanks,
    Tatiana

    1. Hi Tatiana,

      This is not possible with the current script. But we are going to release the Excel add-in soon that will have this feature. Please stay tuned for the update.

  15. Hi Svetlana,
    Thanks for the above code but i guess if we have some unique data in cell along with the color code then there is no need of VBA coding as we can apply simple countif or sumif functions to count/sum the data.

    1. Hi Puran,

      You are absolutely right. This code is purposed for the cases when there is no obvious data / color code relation.

  16. hello,

    thanks for the help. i am trying to use the count by conditional formatting. it is working when a cell's background is colored, but does not work when formatting is for border color.
    also, instead of the count popping up in a box, i would like to have it in another cell.
    could you please help me?

    thanks,
    shachi

    1. Hello Shachi,

      All of the functions count cells by the background color only, the border color is not considered. We are not sure that counting by the border color is feasible, this requires additional research.
      As for the pop-up, it is displayed in case you color cells using conditional formatting. Otherwise, you can use the CountCellsByColor function that outputs the result directly in a cell.

  17. I am trying to do a bit of a hybrid so I can use an if function. My goal is to make it so if a user manually colors a cell red I can then run an if statement on it, when they remove the red it would no longer be true and can run a different formula. I have added and additional column that I will hide when done, I would like to have a way to use the background color in a single cell in a column give me the color index or some sort of color in column b that I could then run a test against. so if reds color index is 4 and cell A1 is red, then I would like 4 or something simular to show in column b. I am sure this is really simple but over my head. I tried adding the first part of the getcellcolor function but it returns the same color for the cell regardless of what color the background is. I would like to be able to do the same thing with font colors.

    I am using excel 2010 and 2013

    Thanks

    1. OK, so I just figured out my problem and it was me, I have it working now. Is there any way to run a macro that will automatically do the f2 part? I am trying to hide the column that has my color index in it so users can't mess it up but now they can't hit f2 and calculate it.

      Thanks, brilliant script

      1. Hi Brad,

        With the current (updated) version of the code, your users can press F2 on any cell, not necessarily the one with the formula.
        Moreover, you can do without F2, by using a solution I posted in response to comment 44 above. Hopefully, this will be helpful for you too.

  18. Hi,
    Svetlana, you are a genius in excel. I like your vb code.
    Thank you.

    1. Thank you very much Sofi!
      But all accolades should really go to our Excel guru Alex : ) As I mentioned in the article he wrote the code for this example.

  19. Hello,

    Thank you for sharing the instructions and code. I'm experiencing some issues with the implementation. When trying to open and run the macro using function Alt F8, the macro does not appear. When I search for the Macro Enabled Workbook file (.xlsm) that I've saved, I see that there is an exclamation mark on the file icon. When attempting to open the .xlsm file, I receive the following error: "Compile Error: Invalid outside of procedure" Could you please help me to figure out what I'm doing wrong? Thank you!

    1. Hi Sarah,

      Please verify whether you copied the entire code from the web-site (from our experience, occasionally the first or last line is missing). If it is not the case, try to add the code anew to some other worksheet. If the error persists, please specify what Excel version you are using.

  20. I would like to write a single IF statement to print a "1" or a "2" in the cell if the colorindex=3. I tried something like =If(range("a1").colorindex=3,1,2)) but something isnt quite correct, I could do the VB code, but i really want something really simple like the IF statement since I am only looking a single cell. I dont want to use "conditional formatting", the color of the cell is set via VB code (selection.colorindex=3)
    Thanks

    1. Hi John,

      Excel's IF function as well as all other functions work with simple expressions and standard Excel formulas only. You cannot use any VBA code inside the IF statement. Also, as far as I remember none of standard Excel functions can return color index. And this was the reason why custom VBA code is needed for this task.

  21. Now, I am getting #NAME? in the boxes where my formula =CountCellsByColor(N102:N334,A335) was entered. Please Help!

    1. Hi Cassy,

      It's hard to say what exactly causes the error without seeing your workbook. If possible, you can send it to us at support@ablebits.com and I'm sure we'll be able to fixed.

  22. PS.. I have saved it as macro-enabled workbook

    1. Hi Khaled,

      The only possible reason that I can think of is that you accidentally added the code to a module of some other workbook. Will you check that please?

  23. thanks for the great support.. but i lose the function when i close excel and reopen it again.. any solution for that?

  24. It worked great but when I closed the page and opened it again, it gave me an error and the module was gone. I saved so i am confused on what is happening. Please help!

    1. Hi Cassy,

      Please make sure you save the workbook as macro-enabled and add the functions to the correct placement.

  25. very useful thanks much. Even though i dont try all as yet.

  26. This worked great! However, I want to do something a bit different and cannot find a solution. I just want to count any colored cell in a row. My rows are dates. The columns represent projects. The colored cells represent different tasks for these projects and the dates they are to be done. These tasks are color coded. I want to limit the concurrent tasks so I want to count number of color-coded tasks. I don't care how many yellow, how many blue, etc. I just want to know how many colored. For example, if there are three yellow, two blue and three orange, I want to see the number 8. Any ideas?

    Thanks,

    1. Hello Tim,

      You can achieve the desired result by counting the non-colored cells (white is also a color : ) and then subtracting this number from cells total. So, add the code to your workbook as described in the article and copy the following formula in the last column of each row, starting from row 2:
      =COLUMN()-1-CountCellsByColor(A2:Q2,R2)

      R2 - the cell with the formula, it should always be non-colored.

      A2:Q2 – color-coded columns that you want to count. If your color coded cells start from any column other than A, than replace -1 in the formula with the corresponding column number. E.g. if your color codes start with column B, the formula will be =COLUMN()-2-CountCellsByColor(B2:Q2,R2)

      1. You're the greatest, thanks!

  27. Hello, every time i attempt to use the sum by color function it says sum function not defined.

    My intended use is to mark a sales order size in red to indicate that it is complete. I do not want red in the total because this is primarily used for ordering more product and I do not want to over order. Thank you for any help!

    1. Hi RyanRo,

      This happens because Excel cannot find some or all of GetCellColor’s functions. Please check the most frequent causes I listed in my reply to comment 42 above.

  28. Hi, I am using the macro for cells that are conditionally formatted. Is there a way to produce the result in a cell versus a message box?

    Thank you for this information!

    1. Unfortunately, this cannot be done using a macro because of some limitations of Excel. We are planning to create an add-in that will cope with this. Please stay tuned!

  29. Thanks for the code. It works beautifully. I am wondering if there is a way to have the sum update automatically when you colour a new cell. I know you said above that it is not possible, but there must be some code that could be written that would allow you to be able to do it.

    I'm not very familiar with VBAs so I am not sure where to start. But couldn't you write a code of some sort that says:

    If "colour change" in "range" then "Run Function"

    Just wondering.

    1. Hello Darcy,

      We have found the solution on the page below and updated our script: https://sites.google.com/site/e90e50fx/home/sum-by-color-calculate-when-color-changed

      However, please not that this script works a bit slower. If it is not critical for you, proceed with the following steps:

      1. Download the updated code from the post.

      2. Create a new Class module and add the following code to it:

      Public WithEvents cX As CommandBars

Private Sub cX_OnUpdate()
  Static s As String
  Static l As Long
  If s = ActiveCell.Address(, , , True) Then
    If l <> ActiveCell.Interior.ColorIndex Then
          ThisWorkbook.ActiveSheet.Calculate
    End If
  End If
  s = ActiveCell.Address(, , , True)
  l = ActiveCell.Interior.ColorIndex
End Sub

      3. In the module with our code, add the following code to the very beginning before the current code.

      Dim cf As New Class1

Sub Auto_Open()
    Set cf.cX = Application.CommandBars
End Sub

Sub Auto_Close()
    Set cf.cX = Nothing
End Sub

      4. For the changes to take into effect, close the workbook and open it anew.

  30. Thanks for the code, your instructions are very clear and it works brilliant.
    However I did experience a problem when copying the code from the website I found it pasted without any line breaks or indents. I was using ie9 but when I changed to chrome everything worked ok. Thought I'd just say in case anyone has similar problems.

    1. Hi Paul,

      Thank you very much for letting us know!

  31. I can't seem to use these functions without getting a #NAME error.

    1. Hi Jim,

      Excel throws this error when it cannot find GetCellColor's functions. The most obvious reasons are:
      - The functions have not been added
      - They were added to a wrong location
      - The workbook was not saved as macro-enabled
      - There's some misprint in the function's names

      Will you, please, verify the items above?

      1. Thanks for the response Svetlana.

        My mistake was trying to call the Function from a hidden macro enabled workbook located in XLSTART while I was in a .xlsx workbook.

  32. Hi Svetlana,

    I am facing an issue with specific colors like dark green and dark red, the formula is mixing both of them, so if i have a dark green cell with value of 5 and another dark red with a value of 7 so in this case the formula is giving me 12 as a result for both colors , need your help please

    1. Hello Gary,

      Thank you very much for your workbook. You are absolutely right, this is Excel's bug. I've tried to optimized the code performance, which is why I used the .ColorIndex property. As it turned out it still has a bug – earlier Excel versions did not allow using more than 56 colors in one workbook. So, 56 is the maximum number that the function can return. In newer Excel versions beginning with Excel 2007, you can use millions of colors at a time. But the .ColorIndex function still returns up to 56 colors even for new colors.

      I've re-written the code using the .Color property that returns a full color code. Please copy / paste it anew, it should work well now.

      Thanks again for your worksheet, it gave me a clue!

      1. Thank you both Alex and Svetlana for your responses & assistance :)

    2. Hi Gary,

      It's hard to say what the cause of the problem is without seeing your colors. Can you please send us your worksheet (only colors, no data is actually needed) at support@ablebits.com? We will try to figure it out and fix the code if needed.

  33. Hi Svetlana,

    Thank you very much for this piece of code, it helped me a lot!
    However, I wonder if there is small bug inside or not
    (I don't know if my "pre" tags with SyntaxHighlighter below will work sorry for the inconvenience)

    The function "GetCellColor" returns ".Color" property :

    Function GetCellColor(xlRange As Range)
    GetCellColor = xlRange.Cells(1, 1).Interior.Color
    End Function

    but the other functions compare the values of ".ColorIndex" properties:

    indRefColor = cellRefColor.Cells(1, 1).Interior.ColorIndex
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.ColorIndex Then
    cntRes = cntRes + 1
    End If
    Next cellCurrent

    Shouldn't we always compare ".Color" properties ?

    Yohan

    1. Hi Yohan,
      This is a very good question! The code is correct. The point is that the .Color property returns the color RGB code, e.g. FFFF00 for yellow; while .ColorIndex returns the color index in Excel's color palette. That is why we use .Color when we need to know what color is used in a certain cell. When we are comparing two colors, we use .ColorIndex as a quicker way.

      1. Hello! Thank you very much for the code! I just added it to my worksheet and it works like a charm (I have also purchased the Suite). One question related to Yohan's: by adding the first VSB module, GetCellColor gives me the MS access color code. I take this is the same as 'Excel's color palette'. Correct?

        If so, question: How do I get the RGB code as output instead of the MS code? And where do I have to put the piece of code in the module above (sorry I am a newbie)

        Another question: how do I search for a single specific cell in a specific color across my worksheet? The input should be the MS code, while the output should be the location of the cell.

        Thank you!
        Vanina

      2. Hi Svetlana Cheusheva how are you i am new qamar
        i want to ask something

        i am doing job as computer operator

        i make result of 8 semesters each semester is come after 6 months BS.Engineering Degree is being given to students in 4 years
        Questions:
        1- I post summer result in some students record because they some subject
        i want when i post their summer result then changing effect shoud be on all semester result,
        2- but before knowing it is important that how can we merge more than one result combine or all results how will be in link
        If you understand my words then kindly give me reply and solve my problems I will be very thankful to you for this kind act

        Naveed

      3. Ok !
        in fact I needed to compare RGB codes, so now evreything is clear

        Thank you for your response

  34. Thank you for providing this code. It has worked well. My worksheet includes colours with a pattern (which represents pending) and I do not want to count these cells.
    Is it possible to add to the code so it does not count the colour if a pattern is also in that cell?
    Thank you
    Tammy

    1. Hi Tammy,

      In theory, this can probably be done. But to be able to say with certainty, we need to have a look at the colors you are using because very close colors may not be recognized by the script. So, if you can send us your workbook (without real data, only colors) and indicate which color(s) is a pattern, we will try to help.

  35. Hi Wanda,

    Sorry, I am not sure I can exactly follow you. If you can send us (at support@ablebits.com) your original workseet and the result you are trying to achive, we will try to help.

  36. I have an assignment that requires counting color blocks per wall (total), per column (total).

    Column South West 1 - Example: F,6 (DB-Dark Blue), G,6 (LG-Light Green), H,6 (A-Amber)
    Row 2
    Type B10

    The Whole point is calculating quantity of color glass per column/wall (4colums per wall).

    Thank you in advance.

    In kind,

    Wanda Rolon

  37. Thanks to you I am able to correctly count cells of a particular colour now in a column, but I really need to be able to count only the rows where both a colour is in a certain column and another has the words "Indoor" in it (i.e. 2 conditions to be met to count the row).
    I tried the formula: =COUNTIFS(Report.xls!$I:$I,$B$9, Report.xls!$AH:$AH,"(CellsByColor(Report.xls!$AH:$AH,A3))") which appears to work but always results in the answer zero. Can you see where I am going wrong please or suggest a better way?

    1. Hi Greg,

      There is no Excel formula that would let you count rows based on two different conditions at a time. This can be done by creating an additional column in your Report.xls sheet. If such an approach is acceptable for you, please let me know and we will try to work out a formula.

  38. Hi, by chance i get this article while surfing. excellent piece of information. How to count cells colour to denote a separate colour. For example, if there are five yellow,five red and 10 oranges in a row, say from a1 to a20. in A21, i want to stipulate a condition, where, there should be 5yellow (not less or more), similarly 5 red and 10 oranges, if these are satisfied, then I want a21 to be green colour otherwise, blank. How to effect it in a selected row and then i can copy the formula.May i expect your reply as solution.

    1. Hi Jraju,

      You can add this function to your workbook and then enter the following formula to A21:
      =AND(CountCellsByColor($A$1:$A$20,$B$1)=5, CountCellsByColor($A$1:$A$20,$B$2)=10, CountCellsByColor($A$1:$A$20,$B$3)=5)=TRUE

      Where $B$1, $B$2 and $B$3 are the cells with your color patterns (yellow, red and orange, respectively).

      If the cells with color patterns are on another sheet, then you need to add the sheet's name, e.g.
      =AND(CountCellsByColor($A$1:$A$20,ColorLookup!$A$1)=5, CountCellsByColor($A$1:$A$20,ColorLookup!$A$2)=10, CountCellsByColor($A$1:$A$20,ColorLookup!$A$3)=5)=TRUE

  39. Hello Jake,

    Regrettably, none of our Excel developers knows how to do this. The Excel Object model does not allow using the needed property in formulas, which is why it's impossible to create a conditional formatting rule to count the colored cells. Calling a macro manually as described in the article is the only possible way that we know.

  40. Hello,
    Thanks for the code. It is working but i have a question of how to set up a rule to use it. I used the Conditional formatting code.and now i am trying make a rule to count the number of green cells automatically for each column and return it to a specific cell. each column is a month and i want to know how many red green, yellow, and orange cells there were for each month. if you could shed some ligght that would be great.

  41. Thank you for adding the code in response to Connor's comment about the entire workbook. I am trying to get the sum, not the count for the workbook. I just want to make sure that code works for the sum as well as the count, and if so, what data range would I use? Thank you.

    1. Hi Josh,

      It's a very good question! It actually made me realize that the formulas we published in that section were not true.

      When using the function that counts and sums colored cells across all the sheets in a workbook, you need to use the following formulas to count and sum, respectively:
      =WbkCountCellsByColor(A1) and =WbkSumCellsByColor(A1)

      We've slightly modified the code and does work for the sum as well as for the count now. Just enter the second formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color (it's (A1) in the example above) and the formula will display the sum of all the cells shaded in this color in your workbook.

      Thanks again for your comment and your great question!

  42. Hi,

    Thanks for the code. It's very useful. I am looking for code that counts coloured cells and then recounts when a filter is applied to those cells. i.e. it counts all the coloured cells in the filtered data. Any help with this would be much appreciated.

    Matt

      1. Thanks for the response.

        Coloured manually - The data was originally conditionally formatted however I copied and pasted it to remove the conditional formatting so the data is effectively coloured manually.

        I am using Excels autofilter.

        Matt

        1. Matt,

          Try to find the following line in the code:
          If indRefColor = cellCurrent.Interior.ColorIndex Then

          And replace it with:
          If (indRefColor = cellCurrent.Interior.ColorIndex) AND (False = cellCurrent.Rows.Hidden) Then

          This line is used both in "Function CountCellsByColor" and "Function SumCellsByColor". You can change it in both functions if you want to count and sum the filtered cells, or in the CountCellsByColor function only.

          Does it work now as you want it?

          1. Thanks. I changed it and now it works perfectly.

            Find the following line
            If indRefColor = cellCurrent.Interior.Color Then

            And replace it with:
            If (indRefColor = cellCurrent.Interior.Color) AND (False = cellCurrent.Rows.Hidden) Then

  43. Hi Boyo,

    Regrettably, we do not know a way to make only one count for the merged cells. Our developers say this is a very tricky with a lot of pitfalls.

  44. Hi Svetlana,

    The code works amazingly and I thank you.
    Could you please assist me in with a code of how to count merged colored cells?

  45. Doesn't work. A window oppens giving the information of error in formula formatting. Any body could help?

    Thanks

    1. Hi Francisco,

      What Excel version do you use? Please note that the script works with Excel 2010 and 2013 only.

  46. Hello, thanks for the sumifcolor~I am manually adjusting the colors after I have entered the formulas, but now the formula will not automatically update. I have verified the "automatic" update is checked...verified other formulas are updating automatically. However, when I add another cell to the color I want to update my sum, it does not do so unless I click on the formula and hit enter.

    Please advise...and thanks!

    1. Hi Ian,

      This is how all macros and VBA scripts work in Excel, you do need to click on the formula and press Enter after changing the colors manually. Please see my comment 10 above for more details. Thanks for your comment!

      1. Thanks! Your site is going to make me look wicked smart. And I appreciate you.

  47. Hi Svetlana,
    Thanks for the code! It's just what I was looking for. For some reason, however, it's not counting correctly. I highlighted 42 rows: 27 black font, 15 red font and it's giving me a count of 16 when it's sent to count black font. I set up =CountCellsByFontColor(A3:A44,A1), where A3:A44 is the range and A1 is the color (black). Please let me know how to fix this. I would love to use this macro!

    1. Hi Svetlana, I deleted the coding and started again and it works now. Must have been my error. Thanks so much!

      1. Hi Cheryl,

        It's great that you've figured it out on your own! And thank you for letting me know.

  48. I believe I may have figured it out; it seems that the code will work for basic colors but will not work for custom colors. Thanks!

    1. John,

      I consulted Alex, our Excel specialist who actually wrote the code, and he says it should work with custom colors too, except for similar colors. BTW, which code have you tried - for cells colored manually or using conditional formatting?

  49. Hi John,

    Did you update a cell with the formula after changing the color manually (put the cursor in the cell, press F2 and Enter)? Please see comments 10 and 12 above for more details.

  50. Hi Svetlana,

    I've tried to use the code to do a count based on color; however, it doesn't seem to be counting correctly since the color I have on a table is red and I am trying to count whenever I manually update it to green (it currently is counting it regardless of what color I choose).

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