SumFont UDF is Rounding Sum Values up+down - Solution needed to fix this problem

[DataQuestioner]

I picked up the following UDF (bottom of this post) from a previous Thread in this Forum started back on 16 Jan 2013.
It is a handy formula for adding up the value of cells in a range which have a specific font colour.

Here's the problem...If the cell values have decimals that individually are less than "xx.5", i.e. 1.45 + 1.35 = 2.80, then the SUM rounds down the values, in this case 2.80 becomes 2.00.
Also, if the cell values have decimals that individually are more than "xx.5", i.e. 1.5 + 1.65 = 3.15, then the SUM also rounds down the values, in this case 3.15 becomes 3.00.

The cells in my table have no conditional formatting or cell number formatting (other than 2 decimal places).

Here's a sample table with some range values, and font colours, and the final sum values based on the UDF...

	A	B
1	200.00
2	100.00
3
4	40.25
5	80.45
6	100.15
7	10.35
8
9	60.00	< Formula in A9 is ...=SUM(A1-sumfont(A1,A4:A7))
10	9.00	< Formula in A10 is ...=SUM(A2-sumfont(A2,A4:A7))

<tbody>
</tbody>

The answer in Cell A9 should be 59.60, and the answer in Cell A10 should be 9.20.

Please assist/explain if you can. Thanks.


'Function to sum the cells inside a range if the font is a certain colour
Function sumfont(Colour_Sample As Range, Cell_Range As Range)
Dim rCell As Range
Dim lCol As Long
Dim lResult As Long
Application.Volatile
lCol = Colour_Sample.Font.ColorIndex


For Each rCell In Cell_Range
If rCell.Font.ColorIndex = lCol Then
lResult = rCell + lResult
End If


Next rCell


sumfont = lResult


End Function

 

[Rick Rothstein]

'Function to sum the cells inside a range if the font is a certain colour
Function sumfont(Colour_Sample As Range, Cell_Range As Range)
  Dim rCell As Range
  Dim lCol As Long
  Dim lResult As Long
  Application.Volatile
  lCol = Colour_Sample.Font.ColorIndex
  For Each rCell In Cell_Range
    If rCell.Font.ColorIndex = lCol Then
      lResult = rCell + lResult
    End If
  Next rCell
  sumfont = lResult
End Function

It looks like the sumfont function is returning a (Banker's) rounded value because its lResult variable is declared as a Long... change it to Double and I think your formulas will work correctly.

 

[DataQuestioner]

Hello Rick - good to hear from you again.

Yes, the "Dim lResult As Double" change gives me the exact answer I'm looking for.

As a good hospital midwife...you always deliver!

Thanks Rick.