I think I'd create a new UDF: =countbycolorandtext()
And pass it one more parameter--the text you're looking for.
Then add a line that checks if the text matches the cell value (within the UDF).
Something like:
Option Explicit
Function CountByColorText(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False, _
Optional Str As String = "") As Long
Dim Rng As Range
Dim CheckStr As Boolean
Application.Volatile True
For Each Rng In InRange.Cells
CheckStr = False
If Str = "" _
Or LCase(Rng.Value) = LCase(Str) Then
CheckStr = True
End If
If CheckStr = True Then
If OfText = True Then
CountByColorText = CountByColorText - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColorText = CountByColorText - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
End If
Next Rng
End Function
And use it like:
=countbycolortext(B10:G23,6,TRUE,"debs")
ps. I took the original =countbycolor() function from Chip Pearson's site:
http://cpearson.com/excel/colors.htm
Paul Sheppard wrote:
>
> I have a range of data in cells G4:O181
>
> I have used =COUNTIF($G$4:$O$181,"Debs") to count the number of
> occurences of Debs
>
> In some of the cells Debs is black text and some Debs is red text
>
> =(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE)) will give me the total
> number of cells that have Red Text
>
> I have tried this formula to calculate the number of cells that are
> both Debs and red text
> =SUM(G4:O181="Debs")*((PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))), but
> get the value zero
>
> How can I combine the two to get the result for the number of cells in
> the range that are Debs and red text
>
> Thanks in advance for any help
>
> --
> Paul Sheppard
>
> ------------------------------------------------------------------------
> Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
> View this thread: http://www.excelforum.com/showthread...hreadid=502384
--
Dave Peterson
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by paul
Bookmarks