Forum Discussion
use sumif function when criteria is cell hihlighted with a color highlight
For summing numbers in cells with a specific fill color:
Function SumByColor(ARange As Range, ColorCell As Range) As Long
Dim ACell As Range
Application.Volatile
For Each ACell In ARange
If ACell.Interior.Color = ColorCell.Interior.Color Then
SumByColor = SumByColor + Val(ACell.Value)
End If
Next ACell
End Function
To sum the values of cells in F4:F8 with the same fill color as C4, use
=SumByColor(F4:F8, C4)
See the attached workbook.
Thanks for uploading this! Better than adding it all on your own. However, when I use it, it seems to omit change. For example, I'm creating a budget and have categories of expenses color coded. When I use this formula, it produces a whole number instead of the exact amount of change if added properly. The values that should be included in gas for example are: $16.87, $23.71, $75.23, $50.01 & $17.31 = $183.13
- jeremyc740Aug 10, 2024Copper ContributorThat would be it! I've had very little exposure to creating macros and typing code, so I knew it had to be something with that when my formatting within the sheet was accurate. Greatly appreciated HansVogelaar!
- HansVogelaarAug 10, 2024MVP
The earlier questions in this discussion were about summing whole numbers. If you want to sum numbers with decimals, change the return type of the function from Long to Double:
Function SumByColor(ARange As Range, ColorCell As Range) As Double Dim ACell As Range Application.Volatile For Each ACell In ARange If ACell.Interior.Color = ColorCell.Interior.Color Then SumByColor = SumByColor + Val(ACell.Value) End If Next ACell End Function