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

#### DataQuestioner

##### Board Regular
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

Last edited:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Rick Rothstein

##### MrExcel MVP
Rich (BB code):
``````'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.

Last edited:

#### DataQuestioner

##### Board Regular
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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,275
Messages
5,836,337
Members
430,421
Latest member
Natas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back