How to count the number and sum of cells by color in Excel 2010 and 2013

Contents

How to count and sum by color in excel sheet
- We count the sum and number of cells by color in the entire workbook
- Custom Functions to Define Cell Fill Color Codes and Font Colors
How to count the number and sum of cells by color, colored using conditional formatting
- How to use the code to count the number of colored cells and sum their values
- Workbook with examples for download

In this article, you will learn how to calculate the number and sum of cells of a certain color in Excel. This method works for both hand-colored cells and cells with conditional formatting rules. In addition, you will learn how to set up a multi-color filter in Excel 2010 and 2013.

If you are actively using a variety of fills and font colors in your Excel worksheets to highlight different types of cells or values, then you will most likely want to know how many cells are highlighted in a particular color. If the cells contain numbers, then you probably want to calculate the sum of all cells with the same fill, for example, the sum of all red cells.

As you know, Microsoft Excel provides a set of functions for various purposes, and it is logical to assume that there are formulas for counting cells by color. But, unfortunately, there is no formula that would allow summing up or counting by color on a regular Excel sheet.

If you do not use third-party add-ons, there is only one solution – to create a user-defined function (UDF). If you don’t know much about this technology or have never heard the term at all, don’t worry, you won’t have to write the code yourself. Here you will find some great pre-built code (written by our Excel guru) and all you have to do is copy and paste it into your workbook.

How to count and sum by color in excel sheet

Let’s say you have a company orders table where the cells in the column Delivery colored according to their values: Due in X Days – orange, Delivered – green, Past Due – red.

Now we want to automatically count the number of cells by their color, that is, count the number of red, green and orange cells on the sheet. As I said above, there is no direct solution to this problem. But, fortunately, we have very skilled and knowledgeable Excel gurus in our team, and one of them wrote flawless code for Excel 2010 and 2013. So, follow the 5 simple steps described below, and in a few minutes you will know the number and sum of the cells of the desired colors.

Open an Excel workbook and click Alt + F11to start the editor Visual Basic for Applications (VBA).
Right-click on the name of your workbook in the area Project – VBAProject, which is located on the left side of the screen, then in the context menu that appears, click Insertion > Modules.
Paste the following code into your sheet:

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

Save the Excel workbook as .xlsm (Macro-Enabled Excel Book). If you’re not too comfortable with VBA, check out How to insert and run VBA code in Excel for detailed step-by-step instructions and tons of helpful tips.
When all the behind-the-scenes steps are done, select the cells where you want to paste the result and enter the function in them CountCellsByColor:
CountCellsByColor(диапазон, код_цвета)

In this example, we are using the formula =CountCellsByColor(F2:F14,A17)Where F2: F14 is the range containing the colored cells you want to count. Cell A17 – contains a specific fill color, in our case red.

In exactly the same way, you write down the formula for other colors that you want to calculate in the table (yellow and green).

If the colored cells contain numerical data (for example, a column Qty. in our table), you can sum the values based on the selected cell color using a similar function SumCellsByColor:

SumCellsByColor(диапазон, код_цвета)

As shown in the screenshot below, we have used the formula:

=SumCellsByColor(D2:D14,A17)

where D2: D14 – range, A17 – a cell with a color sample.

In the same way, you can count and sum the cells by font color using the functions CountCellsByFontColor и SumCellsByFontColor respectively.

Note: If, after applying the above VBA code, you suddenly need to color a few more cells manually, the sum and number of cells will not be recalculated automatically after these changes. Do not scold us, these are not code errors 🙂

In fact, this is the normal behavior of Excel macros, VBA scripts, and User Defined Functions (UDFs). The fact is that all such functions are called only by changing the data on the sheet, but Excel does not regard a change in the font color or cell fill as a change in data. Therefore, after changing the color of the cells manually, just place the cursor on any cell and click F2, and then Enter, amount and quantity will then update. This should be done with any macro you find later in this article.

We count the sum and number of cells by color in the entire workbook

The Visual Basic script below was written in response to one of the readers’ comments (also our Excel guru) and performs exactly the actions that the author of the comment mentioned, namely, it calculates the number and sum of cells of a certain color on all sheets of this workbook. So here is 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

Add this macro just like the previous code. To get the number and sum of colored cells, use the following formulas:

=WbkCountCellsByColor()

=WbkSumCellsByColor()

Just enter one of these formulas into any empty cell on any Excel sheet. The range does not need to be specified, but it is necessary to indicate in brackets any cell with the fill of the desired color, for example, =WbkSumCellsByColor(A1), and the formula will return the sum of all cells in the workbook that have the same color.

Custom Functions to Define Cell Fill Color Codes and Font Colors

Here you will find the highlights of all the functions we used in this example, as well as a couple of new functions that define color codes.

Note: Please remember that all of these formulas will work if you have already added a user-defined function to your Excel workbook, as shown earlier in this article.

Functions that count by color:

CountCellsByColor(диапазон, код_цвета) – counts cells with a given fill color. In the example discussed above, we used the following formula to count the number of cells by their color:
=CountCellsByColor(F2:F14,A17)
where F2: F14 is the selected range, A17 is the cell with the desired fill color.
All the formulas listed below work on the same principle.
CountCellsByFontColor(диапазон, код_цвета) – counts cells with the specified font color.

Functions that sum values by cell color:

SumCellsByColor(диапазон, код_цвета) – calculates the sum of cells with a given fill color.
SumCellsByFontColor(диапазон, код_цвета) – calculates the sum of cells with a given font color.

Functions that return a color code:

GetCellFontColor(ячейка) – returns the font color code in the selected cell.
GetCellColor(ячейка) – returns the fill color code in the selected cell.

So, counting the number of cells by their color and calculating the sum of the values in the colored cells turned out to be not difficult at all, right? But what if you don’t colorize cells manually, but prefer to use conditional formatting, as we did in How to change cell fill color and How to change row fill color based on cell value?

How to count the number and sum of cells by color, colored using conditional formatting

If you used conditional formatting to set the fill color of cells depending on their values, and now you want to count the number of cells of a certain color or the sum of the values in them, then I have bad news for you – there is no universal user-defined function that will sum by color or count the number of cells and display the result in certain cells. At least I have not heard of such functions, which is a pity 🙁

Of course, you can find tons of VBA code online that tries to do this, but all of that code (at least the ones I’ve come across) doesn’t handle conditional formatting rules like:

Format all cells based on their values (Format all cells based on their values);
Format only top or bottom ranked values (Format only the first or last values);
Format only values that are above or below average (Format only values that are above or below average);
Format only unique or duplicate values (Format only unique or duplicate values).

In addition, almost all of these VBA codes have a number of features and limitations that may prevent them from working correctly with a particular workbook or data type. Anyway, you can try your luck and google for the perfect solution, and if you manage to find it, please come back and post your find here!

The VBA code below overcomes all of the above limitations and works on Microsoft Excel 2010 and 2013 spreadsheets, with any type of conditional formatting (thanks again to our guru!). As a result, it displays the number of cells colored and the sum of the values in those cells, regardless of the type of conditional formatting applied to the worksheet.

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 the number of colored cells and sum their values

Add the above code to your sheet as we did in the first example.
Select the range(s) in which you want to count the colored cells or sum by color if they contain numeric data.
Press and hold Ctrl, click on one cell of the desired color, then release Ctrl.
Press Alt + F8to open the list of macros in your workbook.
Choose a macro SumCountByConditionalFormat and press Run (Execute).As a result, you will see the following message:

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

Count is the number of cells of the desired color; in our case, this is a reddish color that highlights cells with a value Past Due.
Sum is the sum of the values of all red cells in the column Qty., that is, the total number of elements marked Past Due.
Color is the hexadecimal code for the color of the selected cell, in our case D2.

Workbook with examples for download

If you are having difficulty adding scripts to an Excel workbook, such as compilation errors, formulas not working, and so on, you can download an Excel workbook with examples and ready-to-use functions CountCellsByColor и SumCellsByColor, and test them on your data.