2 Excel Functions/Formulas to Count/Sum Excel Cells by Color
Category: [Excel] Demo Available
2 Excel Functions/Formulas to Count/Sum Excel Cells by Color
Got any Excel/VBA Questions? Excel Help.
This UDF will sum a range of cells based on their fill colour
The Code:
Function SumColor(rColor As Range, rSumRange As Range) '''''''''''''''''''''''''''''''''''''' 'Written by Ozgrid Business Applications 'www.ozgrid.com 'Sums cells based on a specified fill color. ''''''''''''''''''''''''''''''''''''''' Dim rCell As Range Dim iCol As Integer Dim vResult iCol = rColor.Interior.ColorIndex For Each rCell In rSumRange If rCell.Interior.ColorIndex = iCol Then vResult = WorksheetFunction.Sum(rCell) + vResult End If Next rCell SumColor = vResult End Function
Or, this UDF will count a range of cells based on their fill color
The Code:
Function CountColor(rColor As Range, rSumRange As Range)
''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Counts cells based on a specified fill color.
'''''''''''''''''''''''''''''''''''''''
Dim rCell As Range
Dim iCol As Integer
Dim vResult
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = vResult + 1
End If
Next rCell
CountColor = vResult
End Function
To use these UDF's push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).
Use the Function as shown in the graphic example below.
 |
Amount | |
|||
| $ 10.00 | |||||
| $ 25.00 | |||||
| $ 66.00 | |||||
| $ 25.00 | |||||
| $ 31.00 | |||||
| $ 22.00 | |||||
| $ 65.00 | |||||
| $ 100.00 | |||||
| $ 200.00 | |||||
| Result |
|
||||
| Formula | =SUMCOLOR(B2,B1:B10) | ||||
B2is the cell containing the fill color wanted (yellow).
B1:B10 are the cells to sum IF they have the same fill color as B2
See also:
| Index to Excel VBA Code |
| SpecialCells Method |
| Optimize Slow VBA Code. Speeding Up Slow Excel VBA Code |
| Hide Excel Formulas |
| Stop Screen Flicker in Excel |
| Password Protect Worksheet From Viewing |
See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.