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

DataQuestioner

Board Regular
Joined
Sep 12, 2013
Messages
115
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...

AB
1200.00
2100.00
3
440.25
580.45
6100.15
710.35
8
960.00< Formula in A9 is ...=SUM(A1-sumfont(A1,A4:A7))
109.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:

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,680
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Sep 12, 2013
Messages
115
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,566
Members
414,078
Latest member
Frills

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top