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 SoftwareOffice Tab: Use tabbed interface in Office as the use of web browser Chrome, Firefox and Internet Explorer.
Kutools for Excel: Adds 120 powerful new features to Excel. Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Brings back your familiar menus to Office 2007, 2010 and 2013 (includes Office 365).
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:
Is your problem solved?
Recommended Productivity Tools
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day!
Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
- 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...