SumByColor - without helper cells and with two decimals precision?

[ChristianEllehammer]

I'm using the vba code below to sum cells by their color and it's not really working to my need

1. It rounds the sum to nearest whole number. I need two decimals precision
2. It does some weird stuff, e.g. it sums 2 * 1,5 to 4 but it also sums 2 * 2,5 to 4...

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=SumByColor(A3;B1:B3) = 3
should have been 2,7[/TD]
[TD]RED / 1,5[/TD]
[TD]BLUE / 2,5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=SumByColor(A4;C1:C3) = 4
should have been 5[/TD]
[TD]RED / 1,2[/TD]
[TD]BLUE / 2,5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]RED[/TD]
[TD]BLUE / 2,2[/TD]
[TD]RED / 1,2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BLUE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm using Excel 2016 on a Mac and need this this functionality of summing cells based on their colors, without the use of helper cells and with two decimal precision.

Thank you

Function SumByColor(CellColor As Range, rRange As Range)Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
  If cl.Interior.ColorIndex = ColIndex Then
    cSum = WorksheetFunction.SUM(cl, cSum)
  End If
Next cl
SumByColor = cSum
End Function

 

[MARK858]

Try changing

cSum As Long

to

cSum As Double

Although it isn't causing your issue you also haven't declared cl as a variable.

 

[ChristianEllehammer]

Try changing

cSum As Long

to

cSum As Double

This works. Amazing, thanks!

 

[MARK858]

You're welcome but also see my comment about cl in my edit to my original post.

Function SumByColor(CellColor As Range, rRange As Range)
    Dim cSum As Double, cl As Range, ColIndex As Long

    ColIndex = CellColor.Interior.ColorIndex

    For Each cl In rRange
        If cl.Interior.ColorIndex = ColIndex Then
            cSum = WorksheetFunction.Sum(cl, cSum)
        End If
    Next cl

    SumByColor = cSum
End Function