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(G 4: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

does the count by color work?
=sumproduct(--($G$4:$O$181,"Debs")
,--(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))



--
paul
remove nospam for email addy!



"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(G 4: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

Hi Paul

Thanks for the try but it didn't work

paul Wrote:
does the count by color work?
=sumproduct(--($G$4:$O$181,"Debs")
,--(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))



--
paul
remove nospam for email addy!



"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(G 4: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




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

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(G 4: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

Dave

Thanks, that worked

Paul

Dave Peterson Wrote:
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(G 4: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


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