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

Please Login or Register  to view this content.

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?

Sorry I thought you were talking about two distinct columns ...

Attached I have modified your UDF to suit your needs ...

HTH
Carim

Carim this is great! It does just what I need it to do, thank you so much for your time.

For those who haven't downloaded the file, here is Carim's solution:

The Function:

Option Explicit
Function CountFruits(InRange As Range, Fruit 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 Rng.Value = Fruit.Value Then
If OfText = True Then
CountFruits = CountFruits - (Rng.Font.ColorIndex = WhatColorIndex)
Else
CountFruits = CountFruits - (Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng
End Function

Then amend the formula to suit and put in whichever cell you want the result to appear:
=Countfruits(A1:A10,A2,3,TRUE)

The 'A2' within the formula is the cell which provides the value to count (the fruit type) and the '3' represents the colour red to count only those of that value in the range that are in red font.