ChristianEllehammer
New Member
- Joined
- Aug 18, 2015
- Messages
- 6
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
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

Code:
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
Last edited: