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 and 2013.
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.
- Count by color and sum by color (if cells are colored manually)
- Sum by color and count by color across the entire workbook
- Count and sum colored cells (if conditional formatting is used)
- Filter cells by color in Excel
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.
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 and 2013. 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.
3. Add the following code to your worksheet:
Function GetCellColor(xlRange As Range) GetCellColor = xlRange.Cells(1, 1).Interior.Color End Function Function GetCellFontColor(xlRange As Range) GetCellFontColor = xlRange.Cells(1, 1).Font.Color End Function Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long Dim indRefColor As Long Dim cellCurrent As Range Dim cntRes As Long 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 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 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 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 write the following formula into it:
=CountCellsByColor(F2:F14,A17) where F2:F14 is the range in which you want to count colored cells 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.
If you have numerical data in colored cells (e.g. the Qty. column in our table), you can easily calculate the sum by color, i.e. get the sum of all cells colored in red, yellow and green as shown in the screenshot below:
As you see, we used this 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 formulas
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 with the formula and press F2 and Enter, the sum and count will get updated. Special thanks to Gwen for pointing this out in her comment! The same applies to the other macros you will find further in this article.
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,
=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.
Formulas to get a cell’s background color, font color and color code
Here you will find a summary of all the formulas we’ve used in this example as well as a couple of new ones that retrieve color codex.
Formulas to count by color:
CountCellsByColor– 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– counts cells with the specified font color.
Formulas to sum by color:
SumCellsByColor– calculates the sum of cells with a certain background color.
SumCellsByFontColor– calculates the sum of cells with a certain font color.
Formulas to get the color code:
GetCellFontColor– returns the color code of the font color of a cell.
GetCellColor– returns the color code of the background color of a cell.
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?
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 and Excel 2013 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.
As a result, you will see the following message:
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.
If you want to filter the rows in your worksheet by colors in a particular column, you can use the Filter by Color option available in Excel 2010 and Excel 2013.
The limitation of this feature is that it allows filtering by one color at a time. If you want to filter your data by two or more colours, perform the following steps:
1. Create an additional column at the end of the table or next to the column that you want to filter by, let’s name it “Filter by color“.
2. Enter the formula
=GetCellColor(F2) in cell 2 of the newly added “Filter by color” column, where F is the column congaing your colored cells that you want to filter by.
3. Copy the formula across the entire “Filter by color” column.
4. Apply Excel’s AutoFilter in the usual way and then select the needed colors in the drop-down list.
As a result, you will get the following table that displays only the rows with the two colors that you selected in the “Filter by color” column.
How to increase your efficiency in Excel by 50%, at the very least!
Though this section does not relate directly to counting and summing colored cells in Excel, it still may prove useful if working with Excel workbooks is a major part of your daily work. I would like to take the moment and introduce you our Ultimate Suite for Excel, a collection of 20+ add-ins specially designed to deal with the most tedious, painstaking and error-prone tasks that consume a lion’s share of your time, energy and patience.
These smart tools will help you with:
- Merging data from different tables
- Removing duplicates
- Combining duplicate rows into one
- Merging cells, rows and columns
- Finding and replacing in all workbooks
- Cleaning data in worksheets
- And much more!
Just give them a try (a fully-functional trial version is here) and these handy tools will make common operations in Excel not only faster and easier, but actually enjoyable. If you like these tools, be sure to grab a special coupon code that created especially for our blog readers. It will give you the 15% discount on the Ultimate Suite or any separate product: 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!