Forum Discussion
use sumif function when criteria is cell hihlighted with a color highlight
Once again, please provide an example of what you want to do.
I was wondering how to do the same thing e.g. sum the numbers in column D which are in rows with blue highlighted cells. Or even better sum all the numbers in blue highlighted cells
I tried your formula but may have chosen the wrong cells.
- HansVogelaarJul 01, 2024MVP
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.
- jeremyc740Aug 10, 2024Copper Contributor
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
- 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
- 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!