Forum Discussion
use sumif function when criteria is cell hihlighted with a color highlight
Thanks. See the attached demo workbook. The function used is
Function SumIfColour(SumRange As Range, CriteriaRange As Range, Criteria As Variant, _
ColourRange As Range, ColourCell As Range) As Double
Dim i As Long
Dim r As Double
For i = 1 To SumRange.Count
If CriteriaRange(i).Value = Criteria And ColourRange(i).Interior.Color = _
ColourCell.Interior.Color Then
r = r + Val(SumRange(i).Value)
End If
Next i
SumIfColour = r
End Function
One more, How can i use SumifsColour(
- 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!
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 FunctionThanks 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
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 FunctionTo sum the values of cells in F4:F8 with the same fill color as C4, use
=SumByColor(F4:F8, C4)
See the attached workbook.
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.
Once again, please provide an example of what you want to do.
