Sum spesific cell colour

[joacro]

Hi there
I have a worksheet where I need to sum all the values of a spesific font colour of a colour

The cell that needs to desplay the value is D42 (this can change once more rows are inserted above)

The collum that needed is D

Hope someone can help me with this. It is very urgent

Regards

 

[cytop]

There should be enough here to help: Color Functions In Excel

 

[general_payne]

Hi,

You can use the following user defined function:

'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

Paste it into a new VBA module and to use, enter the formula:

=sumfont(colour_sample,cell_range)

Note:

colour_sample is a cell containing the colour font you want to sum (Eg. if you want to count all red font values within the cell range, choose any cell with red font as the colour_sample).

cell_range is the range containing the data you want to sum if the font colour matches the colour_sample (column D in your scenario).

Thanks

 

[DataQuestioner]

This is a great little formula.

Thanks.

 

[DataQuestioner]

I've noticed that if cell values have partial numbers, i.e. "1.5" or "1.25" that the SumFont is ignoring the values after the decimal in the final SUM count!
Anybody know why this is happening?