How to count and sum cells based on background color in Excel?
Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color.
In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.
The following code can help you count and sum the cells with a certain background color, please do as this:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA: Count and sum cells based on backgroud color.
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean) Dim rCell As Range Dim lCol As Long Dim vResult lCol = rColor.Interior.ColorIndex If SUM = True Then For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = WorksheetFunction.SUM(rCell, vResult) End If Next rCell Else For Each rCell In rRange If rCell.Interior.ColorIndex = lCol Then vResult = 1 + vResult End If Next rCell End If ColorFunction = vResult End Function
3. Then save the code, and apply the following formula:
Count the colored cells: =colorfunction(A,B:C,FALSE)
Sum the colored cells: =colorfunction(A,B:C,TRUE)
A: is the cell with the particular background color you want to calculate the count and sum.
B:C: is the cell range where you want to calculate the count and sum.
4. Take the following screenshot for example, enter the formula =colorfunction(A1,A1:D7,FALSE) to count the yellow cells. And use the formula =colorfunction(A1,A1:D7,TRUE) to sum the yellow cells. See screenshot:
5. If you want to count and sum other colored cells, please repeat the step 4. Then you will get the following results:
With the above User Defined Function, you need to enter the formula one by one, if there are lots of different colors, this method will be tedious and time-consuming. But if you have Kutools for Excel’s Count by Color utility, you can quickly generate a report of the colored cells. You not only can count and sum the colored cells, but also can get the average, max and min values of the colored range.
If you have installed Kutools for Excel, please do as following steps:
1. Select the range that you want to use.
2. Click Enterprise > Count by Color, see screenshot:
3. And in the Count by Color dialog box, all of the colors have been counted.
4. And then click Generate report button, you will get a new workbook with the statistics. See screenshot:
If you want to know more about this feature, please click Count by Color.