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

Also, if the cell values have decimals that individually are

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...

<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

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: