Formula Help

  • Thread starter Thread starter Paul Sheppard
  • Start date Start date
P

Paul Sheppard

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
 
does the count by color work?
=sumproduct(--($G$4:$O$181,"Debs")
,--(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))
 
Hi Paul

Thanks for the try but it didn't work
does the count by color work?
=sumproduct(--($G$4:$O$181,"Debs")
,--(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))
Click to expand...
 
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
 
Dave

Thanks, that worked

Paul

Dave said:
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



http://www.excelforum.com/showthread.php?threadid=502384
Click to expand...
 
Back
Top