How to count and sum cells by color in Excel 2016, 2013 and 2010

I this article you will learn how to count cells by color in Excel and get the sum of colored cells. These solutions work both for cells colored manually and with conditional formatting. You will also learn how to filter cells by several colors in Excel 2010, Excel 2013 and Excel 2016.

If you actively use diverse fill and font colors in your Excel worksheets to differentiate between various types of cells or values, you may want to know how many cells are highlighted in a certain color. If your cells' values are numbers, you may also want to automatically calculate the sum of cells shaded with the same color, e.g. the sum of all red cells.

As all of us know, Microsoft Excel provides a variety of formulas for different purposes, and it would be logical to assume that there are some to count cells by color. But regrettably, there is no formula that would let us sum by color or count by color in a usual Excel worksheet.

Apart from using third-party add-ins, there is only one possible solution - utilize User Defined Functions. If you know very little about this technology or have never heard this term before, don't be afraid, you will not have to write the code yourself. You will find the perfect code (written by our Excel guru) here and all that you will have to do is copy / paste it into your workbook.

How to count by color and sum by color in an Excel worksheet

Suppose you have a table listing your company's orders where the cells in the Delivery column are colored based on their value - "Due in X Days" cells are orange, "Delivered" items are green and "Past Due" orders are red.
The original table with cells colored based on value.

What we want now is automatically count cells by color, i.e. calculate the number of red, green and orange cells in the worksheet. As I explained above, there is no straightforward solution to this task. But luckily we have very skilled and knowledgeable Excel gurus in our team and one of them has written the faultless code for Excel 2010, 2013 and 2016. So, move on with the 5 quick steps below and you will know the number and sum of your color cells in a few minutes.

  1. Open your Excel workbook and press Alt+F11 to open Visual Basic Editor (VBE).
  2. Right-click on your workbook name under "Project-VBAProject" in the right hand part of the screen, and then choose Insert > Module from the context menu.
    Click Insert > Module to add a new user-defined function to your worksheet.
  3. Add the following code to your worksheet:
    Function GetCellColor(xlRange As Range)
        Dim indRow, indColumn As Long
        Dim arResults()
    
        Application.Volatile
    
        If xlRange Is Nothing Then
            Set xlRange = Application.ThisCell
        End If
    
        If xlRange.Count > 1 Then
          ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
           For indRow = 1 To xlRange.Rows.Count
             For indColumn = 1 To xlRange.Columns.Count
               arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
             Next
           Next
         GetCellColor = arResults
        Else
         GetCellColor = xlRange.Interior.Color
        End If
    End Function
    
    Function GetCellFontColor(xlRange As Range)
        Dim indRow, indColumn As Long
        Dim arResults()
    
        Application.Volatile
    
        If xlRange Is Nothing Then
            Set xlRange = Application.ThisCell
        End If
    
        If xlRange.Count > 1 Then
          ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
           For indRow = 1 To xlRange.Rows.Count
             For indColumn = 1 To xlRange.Columns.Count
               arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
             Next
           Next
         GetCellFontColor = arResults
        Else
         GetCellFontColor = xlRange.Font.Color
        End If
    
    End Function
    
    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).Interior.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Interior.Color Then
                cntRes = cntRes + 1
            End If
        Next cellCurrent
    
        CountCellsByColor = cntRes
    End Function
    
    Function SumCellsByColor(rData As Range, cellRefColor As Range)
        Dim indRefColor As Long
        Dim cellCurrent As Range
        Dim sumRes
    
        Application.Volatile
        sumRes = 0
        indRefColor = cellRefColor.Cells(1, 1).Interior.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Interior.Color Then
                sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
            End If
        Next cellCurrent
    
        SumCellsByColor = sumRes
    End Function
    
    Function CountCellsByFontColor(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).Font.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Font.Color Then
                cntRes = cntRes + 1
            End If
        Next cellCurrent
    
        CountCellsByFontColor = cntRes
    End Function
    
    Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
        Dim indRefColor As Long
        Dim cellCurrent As Range
        Dim sumRes
    
    	Application.Volatile
        sumRes = 0
        indRefColor = cellRefColor.Cells(1, 1).Font.Color
        For Each cellCurrent In rData
            If indRefColor = cellCurrent.Font.Color Then
                sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
            End If
        Next cellCurrent
    
        SumCellsByFontColor = sumRes
    End Function
  4. Save your workbook as "Excel Macro-Enabled Workbook (.xlsm)".

    If you are not very comfortable with VBA, you can find the detailed step-by-step instructions and a handful of useful tips in this tutorial: How to insert and run VBA code in Excel.

  5. Now that all "behind the scenes" work is done for you by the just added user-defined function, choose the cell where you want to output the results and enter the CountCellsByColor function into it:

    CountCellsByColor(range, color code)

    In this example, we use the formula =CountCellsByColor(F2:F14,A17) where F2:F14 is the range containing color-coded cells you want to count and A17 is the cell with a certain background color, a red one in our case.

    In a similar way, you write the formula for the other colors you want to count, yellow and green in our table.
    The formula to count cells by background color

    If you have numerical data in colored cells (e.g. the Qty. column in our table), you can add up the values based on a certain color by using an analogous SumCellsByColor function:

    SumCellsByColor(range, color code)

    The formula to sum cells by background color

    As demonstrated in the screenshot above, we used the formula =SumCellsByColor(D2:D14,A17) where D2:D14 is the range and A17 is the cell with a color pattern.

    In a similar way you can count cells and sum cells' values by font color using the CountCellsByFontColor and SumCellsByFontColor functions, respectively.

    The formulas to count and sum cells by font color

    Note: If after applying the above mentioned VBA code you would need to color a few more cells manually, the sum and count of the colored cells won't get recalculated automatically to reflect the changes. Please don't be angry with us, this is not a bug of the code : )
    In fact, it is the normal behavior of all Excel macros, VBA scripts and User-Defined Functions. The point is that all such functions are called with a change of a worksheet's data only and Excel does not perceive changing the font color or cell color as a data change. So, after coloring cells manually, simply place the cursor to any cell and press F2 and Enter, the sum and count will get updated. The same applies to the other macros you will find further in this article.

Sum by color and count by color across the entire workbook

The VB script below was written in response to Connor's comment (also by our Excel's guru Alex) and does exactly what Connor requested, namely counts and sums the cells of a certain color in all worksheets of the workbook. So, here comes the code:

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

You use this macro in the same manner as the previous code and output the count and sum of the colored cells with the help of the following formulas, =WbkCountCellsByColor() and =WbkSumCellsByColor(), respectively. Simply enter either formula in any empty cell on any sheet without defining a range, specify the address of any cell of the needed color in brackets, e.g. =WbkSumCellsByColor(A1), and the formula will display the sum of all the cells shaded with the same color in your workbook.

Custom functions to get a cell's background color, font color and color code

Here you will find a summary of all the functions we've used in this example as well as a couple of new ones that retrieve color codes.

Note: Please remember that all of these formulas will work only if you have added the user-defined function to your Excel workbook as demonstrated earlier in the article.
Functions to count by color:
  • CountCellsByColor(range, color code)- counts cells with the specified background color.

    In the above example, we used the following formula to count cells by color =CountCellsByColor(F2:F14,A17) where F2:F14 is the selected range and A17 is the cell with the needed background color. You can use all other formulas listed below in a similar way.

  • CountCellsByFontColor(range, color code) - counts cells with the specified font color.
Formulas to sum by color:
  • SumCellsByColor(range, color code) - calculates the sum of cells with a certain background color.
  • SumCellsByFontColor(range, color code) - calculates the sum of cells with a certain font color.
Formulas to get the color code:
  • GetCellFontColor(cell) - returns the color code of the font color of a specified cell.
  • GetCellColor(cell) - returns the color code of the background color of a specified cell.

The formula to get the color code

Well, counting cells based on color and getting the sum of colored cells was pretty easy, wasn't it? Of course if you have that little VBA gem that makes the magic happen : ) But what if you do not color cells manually and rather use conditional formatting, as we discussed in these two articles How to change the background color of cells and How to change a row's color based on cell value?

How to count by color and sum cells colored using conditional formatting

If you have applied conditional formatting to color cells based on their values and now you want to count cells by color or sum the values in colored cells, I have bad news - there is no universal user-defined function that would sum by color or count color cells and output the resulting numbers directly in the specified cells. At least, I am not aware of any such function, alas : (

Of course, you can find tons of VBA code on the Internet that attempts to do this, but all those codes (at least the examples I've come across, do not process conditional formatting such as "Format all cells based on their values", "Format only top or bottom ranked values", "Format only values that are above or below average", "Format only unique or duplicate values". Besides that nearly all those VBA codes have a number of specificities and limitations because of which they may not work correctly with certain workbooks or data types. All in all, you can try your luck and google for an ideal solution and if you happen to find one, please do come back and post your finding here!

The VBA code below overcomes the above mentioned limitations and works in Microsoft Excel 2010, Excel 2013 and Excel 2016 spreadsheets with all types of condition formatting (kudos to Alex again!). As a result, it displays the number of colored cells and the sum of values in those cells, no matter which type of conditional formats are used in a sheet.

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

How to use the code to count colored cells and sum their values

  1. Add the above code to your worksheet as explained in the first example.
  2. Select a range or ranges where you want to count colored cells or/and sum by color if you have numerical data.
  3. Press and hold Ctrl, select one cell with the needed color, and then release the Ctrl key.
  4. Press Alt+F8 to open the list of macros in your workbook.
  5. Select the SumCountByConditionalFormat macro and click Run.
    Running a macro to count and sum cells colored using conditional formatting

    As a result, you will see the following message:
    The count, sum and color code of cells colored with conditional formatting

    For this example, we selected the Qty. column and got the following numbers:

    • Count is the number of the cells with a particular color, a reddish color in our case that marks "Past Due" cells.
    • Sum is the sum of values of all red cells in the Qty. column, i.e. the total number of "Past Due" items.
    • Color is the Hexadecimal color code of a selected cell, D2 in our case.

Sample workbook for download

If you have any difficulties with adding the scripts to your Excel workbooks, such as compilation errors, formulas not working and so on, please download this sample workbook with the CountCellsByColor and SumCellsByColor functions ready for use and try them on your data.

Fastest way to count and sum cells by color in Excel - new!

Updated on 1-Jul-2014.

When we published this article, we hoped it would be popular because we used to get a lot of questions about how to count and sum cells by color in Excel. But even in our wildest expectations, we did not think it was going to be that popular! What you see in comments on this page is just a tiny portion of the enormous feedback we have received. So, our team decided to take a step further and create an Excel add-in that would count and sum cells by the color you specify or by all colors in the selected range.

Let me introduce you our brand new add-in - Count & Sum by Color for Excel 2016, 2013, Excel 2010, 2007 and 2003. Once installed, it will place 2 buttons onto the Ablebits Data tab on your Excel ribbon - One Color and All Colors, as you can see in the screenshot below:

The Count & Sum by Color add-in for Excel 2016 - 2003

Count and sum cells by the selected color

You simply click the One Color button on the ribbon and have the Count & Sum by Color pane open at the left of the worksheet. On the pane, you select:

  • The range where you want to count and sum the cells
  • Any color-coded cell
  • Color option - either background or font color

Once done, click Calculate and see the result in the lower part of the pane straight away! Apart from the count and sum, the add-in calculates the average and finds the max and min values. No macros, no formulas, no pain :)
Count and sum cells in Excel by the selected color.

Count and sum cells by all colors in the selected range

The All Colors option works basically in the same way, except that you do not have to choose the color. In the "Show results for" section, you can select any of the following options: Count, Sum, Average, Maximum or Minimum value.

If you want to copy the results to your worksheet, click the Paste results... button at the bottom of the Count & Sum by Color pane.
Count and sum cells by all colors in the selected range.

And here is a list of the main features you will find in the Count & Sum by Color add-in:

  • Count and sum cells by color in all versions of Excel 2016, 2013, 2010, 2007 and 2003.
  • Handle cells color-coded manually and with conditional formatting.
  • Besides counting and calculating the sum, the following functions are available: Average, Maximum and Mininum values.
  • An option to recalculate colored cells automatically when a new range is selected.
  • Copy and paste the results to any location on the current spreadsheet or to any other sheet.

You can download a trial version of the Count & Sum by Color add-in here. Hopefully you will like it, but we are keen to know your feedback, anyway : )

How can I get the Count & Sum by Color add-in?

Currently the add-in is available as part of the Ultimate Suite for Excel. This is a collection of our best tools especially designed to deal with the most tedious, painstaking and error-prone tasks in Excel.

In addition to the Count & Sum by Color add-in, the Ultimate Suite includes about 40 other tools that can help you to:

  • Merge data from different tables
  • Remove duplicates
  • Combine duplicate rows into one
  • Merge cells, rows and columns
  • Find and replace in all workbooks
  • Clean data in worksheets
  • And much more!

If you like the tools, be sure to benefit from a special 15% off coupon code that we provide especially for our blog readers: AB14-BlogSpo

Hopefully, in this article you have found the information you were looking for. If not, you are welcome to post a comment and we will try to help!

You may also be interested in:

438 Responses to "How to count and sum cells by color in Excel 2016, 2013 and 2010"

  1. Brett says:

    Hi,

    Just wanted to say thanks so much for sharing this awesome code.

    Excel is already an amazing tool and people like you just make it better.

    Thanks,
    Brett

  2. Amit says:

    Thanks for the code .. it works great !!

  3. Craig says:

    Great Stuff. Thanks for sharing.

  4. Hitesh Phalak says:

    Thanks for sharing, I have certain merged cells to count in between the range. is there a solution yet for that

  5. Steve Martin says:

    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.

    • Niki Scaief says:

      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.

      • Zach says:

        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)

  6. Steve Martin says:

    Please ignore my previous post I finally figured out my blunders.

  7. Brian Scott says:

    Hi,
    Am running Macro with cells that have both hyperlinks to server as well as conditional formatting of them cells. Have copied the codes from above script (CountCellsByColor in addition to the Conditional Formatting code later in the page) and they fail to capture the cells which are conditionally formatted, returning a result of '0'.
    Moving the data selection area outwith the hyperlink/formatted area returns valid sum data just cant seem to get the Conditionally Formatted cells to be recognised. Ground to a halt here, help...

  8. Prasad R N says:

    You guys are awesome !! It works :)

  9. Neil says:

    This macro for "How to count by color and sum cells colored using conditional formatting" is a big help. Well done. I was wondering if there is a way to take the output that pops up in the Message Box and have that info put into a cell. For instance, the Count is what I am really looking for. Is there a way to have the Count displayed in a cell for the range selected instead of a popup window?

  10. Mehboob Shafie Shaikh says:

    Hi Please help me with how i can use indirect formula in excel 2010 because when i m trying enter formula i am getting following error #REF!
    I am entering formula indirect(select cells) but i am not getting when i am entering formula like this =indirect("Select") i am getting the outcome but not able to use for multiple cell and sheets need your help to get the indirect formula in excel 2010

  11. Rod says:

    This is a fantastic tip !

    Thankyou so much :)

  12. momoe says:

    hi,

    how to count the date between one week or two weeks.

    for example in cell A1 is date. I want to count the date after one week( between 2 to 7weeks).what is the formula I can apply. I got the formula to count for today's date or overdue date countifs(a1:a10,"<=&today(),b1:b10,d15), but I cannot get the formula to count the date in between one week.
    1/8/16
    2/8/16
    3/8/16
    8/8/16
    11/8/16
    12/8/16
    15/8/16
    17/8/16

  13. Mark says:

    I followed the instructions verbatim for counting cells that contain a color and it didn't work. When I try to use the formula, Excel returns #NAME?
    I did save the spreadsheet as a macro-enabled spreadsheet. I even went back into the VBA editor to make sure the code was still there and it was. (and I had copied the code verbatim by selecting it from this website and pasting it.)

  14. Santosh says:

    Hi,

    Your example is good one.

    I am in the same problem, but what about merged cell. How do I count merged cell fill with specific color?

    regardsd,

    Santosh

  15. Brad says:

    Hi,

    I'm using the color count for conditional formatting Excel 2010. How can you have the count be placed in a cell automatically after entering data instead of having to run the rule and only getting the result in a pop-up box?

  16. Carl says:

    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.

    • KarlaBee says:

      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?

  17. Petra says:

    I've seen the question I have a few times but there is never an answer associated. I run reports for my boss and some of them require totaling by sum and count for programs during the month based on conditional formatting. The formal works in that I can see the count and sum but, a) I need to include the numbers in my report and b) the count and sums are in separate boxes. Is there a way to populate the answer into a cell with the needed count or sum based on conditional formatting rather than a pop up box which doesn't work for my boss when I print the report.

  18. Ethan says:

    Thank you, worked great

  19. santhosh says:

    i can not able to see color and sum by color in an Excel worksheet.so i need help from you.please explain it brief

  20. Prasad says:

    Hi,

    Great stuff.. have learnt a lot for your website.

    Need one help regarding sum of cells based on color formatting. The code provided is macro, how to convert this to a function so that I can use it as and when needed

    Thanks,
    Prasad

  21. Phil says:

    Thanks for the great article! I am working on a duel scoring system where each has a numerical score (1-5) and a traffic light score (by filling in the cell colour with red, Amber or green) the two scores are not related. Is it possible to modify the code so that it calculates the average score and fills in the cell colour according to the agreement of the traffic light score (all green = green, all red = red, anything else = Amber)?

  22. Klaas Vaak says:

    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?

  23. Angela says:

    I cannot get the workbook sum function to work

  24. Veronica Williams says:

    I tried your VBA code for counting conditionally formatted cells. I need this code to be a worksheet function. I have over 80 columns of data to apply the code to. It is not practical to select range and reference cell, the press Alt+F8 80 times.

  25. AgaK says:

    Count for colors works super slow but perfectly! Thank you so much.

  26. Jesther says:

    Thanks mate :) this is very useful and helpful

  27. coach coveney says:

    I have set up an excel sheet tracking the performance of kids in sport. The columns in question return a result of the test in question. Based on the result, it will be colored, green, yellow, brown or red.

    I copied the text you recommended and put it into the Macros window as instructed. I then tried counting how many cells returning numbers with the different colors mentioned above, using the "CountCellsByFontColor" but I couldn't get it to work, regardless whether the text was bold or otherwise.

    can you help me with this

  28. Greville says:

    Thank you for a great post. I was able to implement for a colleague in 5 mins (2007). Use of "key" or reference cell is great - no need to find the color code. Well done!

  29. Steve says:

    One simple update that might be useful in the original code is to skip over hidden cells, like those hidden by a filter. Here's what I did to CountCellsByColor:

    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).Interior.Color
    For Each cellCurrent In rData
    If indRefColor = cellCurrent.Interior.Color And cellCurrent.Rows.Hidden = False Then cntRes = cntRes + 1
    Next cellCurrent
    CountCellsByColor = cntRes

    End Function

  30. Juliet says:

    Thank you! its really a bbbiiiiiigggg help! Works fine with me :)

  31. Mark says:

    Hi, can you tell me how to use the CountCellsByColor formula if the range isn't adjacent cells. For example, I want to count cells if a certain colour where the range is A1, D1, G1 rather than A1:G1?

    Thanks.

  32. Michael Rosen says:

    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

  33. prakash says:

    Hi, I am doing a project. I have excel file with huge data. I want to add datas between two randomly placed data between two rows with same color or same name. I would appreciate any help!!!!
    Thanks!!!

  34. harky says:

    hi,
    i trying to use =CountCellsByFontColor(V3:V21;Q3)
    but this code dont work well if it count by horizontal

  35. Sakura22 says:

    Hi...

    I'm using a multiple data and to make it simple i'd prefer to use macro that can easily count the color, wherein if color green it gives 40 points but if it less than the desired target count of 5, it gives 0 point. I'm trying to use the CountCellsByFontColor and i got an answer, yet my data is complicated due to some conditions like sample below.

    Sample:
    Target: 99.30% yield
    Color Code if On-Target: Green Below Target: Red
    Target Attendance Performance at least: >=5

    Condition, yes you hit the target yield based on overall output and you got color green, but you're only 4 in attendance, so, instead of giving 40 points, you will receive 0 point.

    Thank you in advance.

  36. James Duffy says:

    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?

  37. Chris K says:

    Hi

    I love your code. Thanks for making it available.I am using a filtered list and have added the suggested modification from reply 32 so it only counts filtered lists (which is great and is working)

    It works but not quite for what I am trying to achieve.

    Its a school and its tracking grades for males and females. In effect I have one column where the autofilter can be M (for boys), F (for females), or both (for all students). I then have a second column where the colour coded grade is (it can be red, amber, green)

    What I want to do is to be able to filter by Boys for example, then count in the grade list all red grades, amber and green grades for boys. Then do the same for girls. Then just leave boys and girls ticked in the autofilter

    I guess what I am trying to do is to count all the red colours in my filtered list by girls and then in the same filtered list all the red ones by boys, then same for amber then green and display all in the same place the totals for each

    I have tried combining countcellsbycolor with IF statements, countifs, sumproduct and so on. But I cant get it to work right.

    What seems to happen mostly is that will boys and girls ticked, I am getting the count of all red boxes or boys and girls but not breaking them down into how many boys and how many girls. Same for amber, same for green.

    Please can you tell me if there is a way to combine countifs, if, sumproduct etc to count the red amber green by gender for the various combinations of just boys, just girls or both in the autofilter

    Can happily send spreadsheet

    Thanks

  38. Adam says:

    Hello,

    I am new to excel/vb/macros and I have an issue that I can't resolve. I've tried to manipulate your code but I'm messing it up without desired output.
    I want to check predefined range of cell for cells that have conditional formatting for background color (i.e. green) and list and highlight all green cells that don't have any value in them. I've found a code that is supposed to do almost exactly what I want but it doesn't work in my case - no results displayed. Your comments would be appreciated.
    Regards,
    Adam

    Sub Red_id()
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    Dim i As Integer
    i = 1
    Do Until i = 11
    If ws.Range("C" & i).Interior.Color = RGB(255, 0, 0) Then
    Debug.Print "C" & i & " is red!!"
    End If
    i = i + 1
    Loop
    End Sub

  39. Somvir Singh says:

    Hi Friend,
    I just want to know, hot to color the cells by using a single cell value.

    ie. - Cell one value - 20
    Want to fill specific color in next 20 cells....

  40. Ruby says:

    Hi,

    Thank you so much for the code. It worked perfectly for me until I need the sum of cells in a certain font color while the background colors of these cells are different. It seems to be only adding the cells with the same background color as the cell that defines the font color, and leaving out those cells with a different background color. Anyone know how to solve this?

    Thanks in advance!!

  41. Saurabh gupta says:

    how to add cells having same background color across different workbooks

  42. Jason says:

    I would like to thank you for this wonderful tutorial. This has helped me tremendously. I currently use your code to count how many passes (green) and fails (red) i have on a particular column on a specific worksheet in my workbook. Once the data is populated, I use a pivot to auto update a chart for reference. I would really like to use conditional formatting on that column, so a if a user selected pass, it would automatically turn green, but when I do this, the code stops registering color. I've tried using the count by color and sum cells colored using conditional formatting but I think i'm doing something wrong. I can't get it to work. Can you assist?

  43. ExcelMan says:

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

    • ExcelMan says:

      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

  44. Joshua says:

    Hi,

    The VBA worked wonderfully. That's a smart coding.

    Thanks.

  45. Peshiyaboy says:

    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.

    • Mary Trifuntova (Ablebits.com Team) says:

      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.

  46. Doug Moeller says:

    Just having a bit of trouble running the SumCellsByFontColor Macro. I'm hoping someone can give me a tip here; I'm new to Visual Basic.

    Whenever I run the Macro, I get a "Compile Error, Syntax Error" with the "Dim indRefColor As Long" highlighted in Blue. (I can send a screenshot if that works better.)

    In all cases, I have entered only cut'n'paste data from that shown above - nothing entered manually.

    What am I doing wrong. I could easily believe I'm leaving out a step or two.

    Thanks.

  47. Lezanne Bester says:

    Thank you so so much! It works perfectly!

  48. BR says:

    Hi there,
    I have been trying to use the code and I think the VB part is ok. However, when I try to type in the function I get an error as I enter the criteria section. for example =countbycellcolor($c$33:$c$66,A5) excel gives me an error when I click on the A5 and says this format is not correct. I use excel 2013

  49. VInce says:

    Very pleased with this material on how to count numbers of cells with certain colours, and that's all I want to report here. I nearly always would enter a code, like 1 or 2 to a cell to record its category and use countif, but a particular file I am working on needs to be more visual and has a macro button or two to 'click' certain colours into cells, so when I needed to count them up I was stuck. But not now! Thank you.

  50. VInce says:

    FORMULA UPDATING? PLEASE TRY & SEND AN ANSWER TO THIS ONE :-):
    I emailed 7 hours ago that the AbleBits method shown for counting colors was the very solution to the problem I was having. I have just noticed something, even so, and I therefore have a question:
    I seem to need to go into edit mode (F2) with some cell or other, after a change to the colour of a cell has just occurred, in order for the formula to update. Otherwise I may have just created, say, 2 cells with a particular font instead of 1, and the count has been saying 1. I find that the count number (1) does not update immediately to 2 - not until I edit and enter some cell or other. That brings about some updating across the spreadsheet which includes this UDF. Whereas an 'ordinary' formula will update as soon as the data, on which it is based, has been altered. How can I get around that? Hope you can help me here, thanks.
    LOTUSMAN

  51. Raul says:

    Excelent Tool I was loocking for that since many days ago and now I got it thanks for post that friend

  52. Fede says:

    Great routines!!!
    BTW, given that some of them helped me, I generated a new function called "SumProductCellsByColor(Range 1 column, cell with color pattern, colum 2)". So, if someone want to take advantage of this code please send me an email I will share it.

  53. JARED says:

    Hi there, working on Excel 2016. Whenever I copy and paste formula after inserting module entered. The message appears "the formula type contains an error". The cells all have wording in which is why I utilised the first module above.

  54. Tossici says:

    Same for me in excel 2016, "the formula type contains an error". The debugger highlights: Function SumCellsByColor(rData As Range, cellRefColor As Range).

    • Hello,

      if you still need assistance with your task, could you please send us your workbook to support@ablebits.com? Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved. Or you can replace any important information with some irrelevant data, just keep the format.
      Don't forget to link this comment in your message.

      Our technical specialist will take a look at your task and try to help.

  55. steveparadox says:

    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.

    • Steveparadox says:

      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

  56. Jeff says:

    Probably doing something wrong. However, when trying any of the functions receive #VALUE! error (when stepping through, appears to occur immediately). Any suggestions? Thanks

  57. Jeff says:

    Please disregard previous question (#256), when copy/paste from latest file worked fine (for some reason could not from instruction page). Thanks for solution - much appreciated.

  58. Sohaib says:

    Thanks and really appreciate your efforts. Liked your code for the Count of cells through Conditional formatting. Is it possible to display the results in excel cells also?

  59. Hayley says:

    Thank you for this code. I have used it to count cells by their conditional formatted colour. What I would like to do is push this a step further and be able to define the range of cells to count and also the colour to look for before having the result entered into a cell for further calculating to be done with it. Any idea how to do this?

  60. Vipul Gupta says:

    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.

  61. nidhin says:

    thank u

  62. Zeta Politi says:

    I have a problem with the formula SumCellByFontColor.
    It doesn't sum the correct number. Meaning the correct amount is 5122,45 and it sums up 5121,00. Cell range is right. Decimals are correct. Am I suppose to do something else to correct this?
    Thanks in advance.

    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  63. Don says:

    This code is great. With the count on conditional formatting why when running the macro the results are not show in the spreadsheet? Is that possible?

    thanks

    • Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  64. Lara says:

    This code worked fine, but it made inputting data on my spreadsheet incredibly slow, everytime I type something Excel freezes and it takes at least 5 seconds so I can type again. And I don't even have that many lines: 600 at the moment.

    Am I doing something wrong or is this code that poorly optimised?

  65. Margaret says:

    I love this formula, but I am struggling with running an "If" Formula with it so I can use 3 basic colors instead of 9 (or potentially more).

  66. Margaret says:

    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!

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

  67. Jenny says:

    Thank you, thank you, thank you! This worked out perfectly!!

  68. Allister says:

    I just want to say thank you VERY much for this easy to use and detailed tutorial! Top quality! I am bookmarking your site for future use! Great job!!

  69. Anna says:

    Hi, But what if I'd like to count if & color? Can I combine Countifs and CountCellsByColor?
    Example order date 20-Okt-13 (column B) & Red (Column F)?

  70. Nancy Armstrong says:

    you are fabulous! Thank you so much!

  71. ASHUTOSH DUBEY says:

    the code is not working.
    my cell shows #NAME?
    its an error. can u rectify it?

  72. Veronika says:

    Hi,
    this is amazing tool, but I have a little problem. If I want to use a conditional formatting, the code doesn't work. The font is changing to red via conditional formatting, but the code sees it like black. After I change color of font handly to red, everything is OK.
    Can you help me please? Thank you very much.

  73. LL says:

    This worked like a charm (once I got my fixed my slightly mismatched colors). I will pay this good deed forward when the situation presents itself!

    Thank you!

  74. Joe says:

    Question - How would I add in that with the color it also needs to find a certain name in the sheet before it adds the total together?

  75. Matt` says:

    Hi Everyone same issue as 72 above if using conditional formatting it results in a 0 return where as if i manually fill the cell to the desired colour it then returns a 1.

    if there a way of counting the cells bases on the conditional formatting of those cells

  76. Tom says:

    I have the functions like CountCellsByColor and same for fonts, thank you very much, They help a lot.

    I am using these for my job tracking sheet, How did the job get created? What is the current status?

    Is there a function that lets me combine the 2 features?
    Something like
    If Count, CellRange, Cellcolor=Green and fontcolor=Red then Add 1.

    In what I am doing, I will never add the numbers together, they are all job numbers, all I need is just the the count of true to both checks.

    Thanks

  77. Tont says:

    Thanks for Sharing, this is awesome

  78. Robert says:

    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?

    • Simon Griffiths says:

      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)

  79. Sadekul Islam Polash says:

    Hi,
    I am Polash. I Can not run this code. I try step by step. Last 3 days I try to very much but not success. Please solved this. What can i do.? I need this formula for my office work......

    Without VB code can not run this.....?

  80. Simon Griffiths says:

    I'm having an issue that I can’t work out that combines applying Conditional Formatting to number cells (the amount paid) based on the date in the cell to the left of them (the date the payment was made). This date needs to be between the dates stated in other cells i.e. 'check that date is greater than date in cell ai10 but less than that in aj10. If it meets that criteria then I want it to colour the amount paid cell the colour I have selected in the Conditional Formatting. Can anyone help?

    I have used the above VBA and formula in this article which looks like it is working so that once I have the above sorted and the cells are automatically coloured I can then get a total amount paid between the 2 stated dates.

    Thank You
    Simon

  81. DonaldK says:

    Many Thanks. I tried a half dozen different solutions from as many websites and this is the only one that worked flawlessly.

  82. Bradley says:

    Awesome, thanks very detailed and works perfectly, not often when I try one of the solutions i searched for does it work first time!!!!!! This does THANKS

  83. Adrienne says:

    Thank You so much for putting this out there! Was just what I was looking for and more!

  84. Simont485 says:

    Is it possible to count a coloured cell by contents.

    Example:
    B2 contains the text 'E1' with no fill and cells B3 and B4 contains 'E1' with a blue fill. B5 is a blank a blue cell.

    In B6 I would expect the answer to be 2 as I only want to count the blue fill cells with E1....

  85. guy says:

    bless you!

  86. James says:

    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.

    • Doug says:

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

      • James says:

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

  87. James says:

    Another thing I struggle with is the syntax of:
    =INDEX(C3:C520,MATCH(0,COUNTIF(C3:C520),AA323))

    I want a count of unique names in column C that matches the criteria in AA323.

    I'm trying to show # of DBs in resolved status when DB Name and Status are in separate columns.

  88. Larry says:

    Dear Ablebits

    Thanks for your work! When I run use the countcellbycolor function it returns an error:

    Compile error: Syntax error

    The debugger points to this line:
    Dim indRefColor As Long

    I'm using the code in Excel 2016 on Windows 10. A number of other people have the same problem. Would you be able to resolve it?

  89. CSUK2018 says:

    Hi,

    The VBA works brilliantly when I manually colour the cells, but it is not work with cells that have been condtionally formatted. Any suggestions?

    Can you help

  90. FAHEEM says:

    =countcellsbycolor(range,criteria)
    it is not working
    i want to count colored cells and font please help me in this matter
    thanks

  91. miri says:

    good job! but when using other kind of conditional formatting such as "top 10 item" or "color scale" or "icon set", the macro doesn't work. do you have a solution for these kind of conditional formatting?
    thank you

  92. Sreejith Nair says:

    Hai Dear,
    I can see, everybody is using this stuff in very friendly and good, unfortunately its is not working with me, may am not much expert in Excel. Please any one can help me to find the solution.

    I filled with 4 different colours (RED, BLACK, YELLOW & GREEN) from A1 to F55, my requirement is need to find how many Red, Black,Yellow & Green.

    Someone can help me for this??

  93. Praveen Kohinkar says:

    i tried this on worksheet where cell contains numerical values. =CountCellsByColor(F2:F14,A17) formula is not taking count ,pls help

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite 2018.3 Summer Offer