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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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