Forum Discussion
use sumif function when criteria is cell hihlighted with a color highlight
I want to use sumif function to add a column of numbers when criteria is a fill color in the cells. How do I do this.
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
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- That 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!
