use sumif function when criteria is cell hihlighted with a color highlight

jeremyc740 

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

HansVogelaar

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

It works! Thank you RLAJack 

RLAJack 

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.

HansVogelaar 

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.