This post will guide you how to count and sum cell by background color in excel. How to get the sum of colored cells in the current worksheet. How do I count cells by background color of cells with user defined function in excel VBA. How to Sum cells by background color in excel.
Count and sum cells by background color
There is no particular function which use to count or sum cells by color in Microsoft excel, so you can try to write a new user defined function to sum or count cells based on background color.
Suppose you have a table where the range of cells in the current worksheet are colored. And if you want to count the number of colored cells, such as, the number of blue or red color in this range of cells, you can create a new user defined function to reflect this requirement.
So you can create the below user defined function to count and sum cells by background color of cells in Excel VBA, just refer to the below steps:
1# open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.
2# then the “Visual Basic Editor” window will appear.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Function CountColorByBgColor(sColor As Range, sRange As Range, Optional SUM As Boolean) Dim gResult Dim sCell As Range Dim sCol As Long sCol = sColor.Interior.ColorIndex If SUM = True Then For Each sCell In sRange If sCell.Interior.ColorIndex = sCol Then gResult = WorksheetFunction.SUM(sCell, gResult) End If Next sCell Else For Each sCell In sRange If sCell.Interior.ColorIndex = sCol Then gResult = 1 + gResult End If Next sCell End If CountColorByBgColor = gResult End Function
5# back to the current worksheet, try to enter the below formula to count cells by background color in Cell F2:
You can see that the number of red color of cells in range B1:C4 has been calculated to Cell F2.
If you want to count the number of others color in the range B1:C4, you just need to drag AutoFill handle down to other cells.
If you want to sum the cells by background color in the range B1:C4, you can write down the following formula using the above user defined functions:
So you just need to set the third argument as TRUE, and it will do SUM action to sum colored cells.