How to count and sum cells by color in Excel

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, Excel 2016, and Excel 2019.

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.

The sample workbook with the CountCellsByColor and SumCellsByColor functions is available for download the end of this post.

Fastest way to count and sum cells by color in Excel

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 to our brand new tool - Count & Sum by Color for Excel 2019 - 2007. 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.

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 over 60 time-saving tools that will help you merge data from different worksheets, remove duplicates, compare sheets for matches and differences, and a lot more!

Available downloads

Macros to count and sum cells by color (.xlsm file)
Ultimate Suite 14-day fully-functional version (.zip file)

You may also be interested in

Category: Excel Tips

Table of contents

606 responses to "How to count and sum cells by color in Excel"

  1. Nirasha says:

    Thank You so much. This is really useful.

  2. Sonia D Spears says:

    Excel by color coding

  3. Rachel Friedman says:

    I tried the countcellsbycolor formula, it just plain did not work! i followed all instructions right & got the #NAME? dumb error over 7 over again.

  4. Ahmed says:

    i need to count multi color in big range contains of about 1000 cells could you help please

  5. Ahmed says:

    now it is working thanks

  6. Dana says:

    This was awesome and so easy to implement. Thank you so much!!!

  7. James Duley says:

    I have been helping my wife with some spreadsheets. Everything was going great until she needed to count employees and the new new employees were typed in red font and were not counted in the total number of employees. Your CountCellsByFontColor worked perfect.

    Thank you very much!!!

  8. Johnny says:

    I was trying few times failed until found your tips, it is working well, thank you so much for your sharing

  9. Ahmed says:

    Thank you so much, it was really helpful!

  10. Tara Fountain says:

    It works but it only does one row. When I try to copy the formula down it doesn't work. HELP!

  11. Maya says:

    Thank you so much for the count by color and sum cells colored using conditional formatting. It's my problem but simple it has an error in something. When I format some spreadsheets with conditional to color. Then use the formula CountCellsByColor(range, color code). The result is wrong. So I hope have good news about it

  12. Sharon says:

    I have an Excel sheet that I am trying to use as a temporary scheduling tool. It is set up to look like a gantt chart with the colors set with conditional formatting based on how long each step in the scheduled process will take to accomplish. I'm trying to figure out how to count the times each conditionally formatted color shows up in a weekly bucket to calculate my labor needs. Is there a way to amend your VBA to read just a portion of my table? Or any ideas on how to accomplish this?

  13. Louise says:

    I am trying to collect data that is in certain color boxes on my spreadsheet to total up amounts of hours someone has done over a certain amount of time. I am pulling my hair out trying to find a solution. Can you please help before i go bald lol.

    • Hello!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

      • Louise says:

        My spreadsheet is a register so i need to add up different color boxes. I have 6 different colors for the aims that i need to add up. So with one color (blue) it is looking for how many hours that have completed over a 43 week programme. There is two things that i want to do. I want to add the amount of blue boxes there are but i also need to know the total of hours they have completed over this time.

  14. Linda says:

    This is great! The only problem I've having is that the cell color doesn't get counted unless I use the format painter tool to copy the color from the cell in the formula. It also isn't working for conditional formatted cell colors. Any help is appreciated!

  15. Nat Castillo says:

    Many thanks, very useful reference, worked like a charm.

    More power & best regards.

  16. Amina says:

    Excellent article and it worked perfectly! Thank you!! Well done!

  17. Charlotte L Tanguay says:

    Amazing!

    This is exactly what I needed.

    Thank you for sharing :)

  18. Mara says:

    Good morning,

    I've tried the macro but it keeps giving me zero as result both for sum and count :(
    HELP!

    Thx,
    Mara

  19. Sajid Khan says:

    Hi
    I tried to use the function but every time I am getting #name error. What should I do?

  20. Anna says:

    LOVE this code - thanks so much!

    In my sheet I have dates in Row 7
    And data in the rows under it

    I have a start date in Cell E4, and a end date in column d starting at D8

    I want to only sumbycolour when the dates are equal to or inbetween the 2 specified dates

    So Start Date is 12/11/21
    End Date is 16/11/21

    The row with the data i'm counting has data in it for the whole year

    I feel like I need to include an IF statement - but getting lost - appreciate anyones help!

  21. Nola Rich says:

    I have a table that lists all shipments not yet invoiced. I have colored all the rows using conditional formatting. As an example, shipments that are overdue for invoicing are colored red, based on more than 5 days past the arrival date of the vessel. We have several managers responsible for different types of commodities, e.g. bulk oils and petroleum, frozen foods, chemicals, etc.
    Can the CountIfs count how many reds, blues, greens, and yellows by manager? Do you have a code that can do this for me? At the moment I am counting manually.
    I have tried =CountCellsByColor(DBN!$B$6:$B$1299,SUMMARY!$C$3) but this results in total blue cells, not how many blue cells a particular manager has.

  22. Rafael Arias says:

    Worked perfect

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