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.
Recommended Productivity ToolsOffice Tab: Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Kutools for Excel: 200 new features for Excel, make Excel much easy and powerful, increase productivity immediately.
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. You can filter the color cells based on your needs. For example, you want to count the cells whose background is generated by standard formatting (or conditional formatting), you can filter as below:
(1) Click the Color method box and then select Standard formatting (or Conditional formatting) from drop down list;
(2) Click the Count type box and select Background from drop down list.
4. And then click Generate report button, you will get a new workbook with the statistics. See screenshot:
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
200 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...