Count by value AND by font colour

Hello,

I'm trying to produce a formula for a column of cells that counts by value AND by font colour. I have 2 formulas that can do each separately but I am having difficulty combining them. These are the two separate formulas:
=COUNTBYCOLOR(H5:H150,3,true) which calls a function that counts all the cells with values in red font
and
=COUNTIF(H5:H150,"apples") which counts all the cells of the value "apples".

Please could anyone help? Thank you.

Hi,

Best solution is to modify your UDF countbycolor() to include the additional condition ...of a given fruit ...


HTH
Carim

Thanks for your reply Carim.

I've tried twice to ask for help on this query on the programming section as it now appears to be a programming query but for some unexplained reason the thread keeps being deleted!... I shall try again here!...

I only have limited VBA experience so I don't know how to expand on the COUNTBYCOLOR function as suggested with the conditional values. This is the code I have been using which I have obtained from another website:

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long

Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

I still want to be able to call the function through a formula into any cell. I
would really appreciate any help on this.

Hi,

A shortcut might help ...

=COUNT((COUNTBYCOLOR(A1:A10,3,TRUE)),(COUNTIF(E1:E10,"apples")))

HTH
Carim

Hi, I have been trying your formula but it isn't working! It seems to count those 'apples' not of the font colour instead of those of that font colour. I have tried amending the formula but still no joy. Are there any other suggestions to this?