software

I have a range of data in cells G4:O181

I have used =COUNTIF($G$4:$O$181,quot;Debsquot;) 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=quot;Debsquot;)*((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: www.excelforum.com/member.php...oamp;userid=24783
View this thread: www.excelforum.com/showthread...hreadid=502384does the count by color work?
=sumproduct(--($G$4:$O$181,quot;Debsquot;)
,--(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))
--
paul
remove nospam for email addy!
quot;Paul Sheppardquot; wrote:

gt;
gt; I have a range of data in cells G4:O181
gt;
gt; I have used =COUNTIF($G$4:$O$181,quot;Debsquot;) to count the number of
gt; occurences of Debs
gt;
gt; In some of the cells Debs is black text and some Debs is red text
gt;
gt; =(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE)) will give me the total
gt; number of cells that have Red Text
gt;
gt; I have tried this formula to calculate the number of cells that are
gt; both Debs and red text
gt; =SUM(G4:O181=quot;Debsquot;)*((PERSONAL.XLS!CountByColor(G 4:O181,3,TRUE))), but
gt; get the value zero
gt;
gt; How can I combine the two to get the result for the number of cells in
gt; the range that are Debs and red text
gt;
gt; Thanks in advance for any help
gt;
gt;
gt; --
gt; Paul Sheppard
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Paul Sheppard's Profile: www.excelforum.com/member.php...oamp;userid=24783
gt; View this thread: www.excelforum.com/showthread...hreadid=502384
gt;
gt;


Hi Paul

Thanks for the try but it didn't work

paul Wrote:
gt; does the count by color work?
gt; =sumproduct(--($G$4:$O$181,quot;Debsquot;)
gt; ,--(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE))
gt;
gt;
gt;
gt; --
gt; paul
gt; remove nospam for email addy!
gt;
gt;
gt;
gt; quot;Paul Sheppardquot; wrote:
gt;
gt; gt;
gt; gt; I have a range of data in cells G4:O181
gt; gt;
gt; gt; I have used =COUNTIF($G$4:$O$181,quot;Debsquot;) to count the number of
gt; gt; occurences of Debs
gt; gt;
gt; gt; In some of the cells Debs is black text and some Debs is red text
gt; gt;
gt; gt; =(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE)) will give me the total
gt; gt; number of cells that have Red Text
gt; gt;
gt; gt; I have tried this formula to calculate the number of cells that are
gt; gt; both Debs and red text
gt; gt; =SUM(G4:O181=quot;Debsquot;)*((PERSONAL.XLS!CountByColor(G 4:O181,3,TRUE))),
gt; but
gt; gt; get the value zero
gt; gt;
gt; gt; How can I combine the two to get the result for the number of cells
gt; in
gt; gt; the range that are Debs and red text
gt; gt;
gt; gt; Thanks in advance for any help
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Paul Sheppard
gt; gt;
gt; gt;
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Paul Sheppard's Profile:
gt; www.excelforum.com/member.php...oamp;userid=24783
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=502384
gt; gt;
gt; gt;--
Paul Sheppard------------------------------------------------------------------------
Paul Sheppard's Profile: www.excelforum.com/member.php...oamp;userid=24783
View this thread: www.excelforum.com/showthread...hreadid=502384I 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 = quot;quot;) As Long

Dim Rng As Range
Dim CheckStr As Boolean
Application.Volatile True

For Each Rng In InRange.Cells
CheckStr = False
If Str = quot;quot; _
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,quot;debsquot;)

ps. I took the original =countbycolor() function from Chip Pearson's site:
cpearson.com/excel/colors.htmPaul Sheppard wrote:
gt;
gt; I have a range of data in cells G4:O181
gt;
gt; I have used =COUNTIF($G$4:$O$181,quot;Debsquot;) to count the number of
gt; occurences of Debs
gt;
gt; In some of the cells Debs is black text and some Debs is red text
gt;
gt; =(PERSONAL.XLS!CountByColor(G4:O181,3,TRUE)) will give me the total
gt; number of cells that have Red Text
gt;
gt; I have tried this formula to calculate the number of cells that are
gt; both Debs and red text
gt; =SUM(G4:O181=quot;Debsquot;)*((PERSONAL.XLS!CountByColor(G 4:O181,3,TRUE))), but
gt; get the value zero
gt;
gt; How can I combine the two to get the result for the number of cells in
gt; the range that are Debs and red text
gt;
gt; Thanks in advance for any help
gt;
gt; --
gt; Paul Sheppard
gt;
gt; ------------------------------------------------------------------------
gt; Paul Sheppard's Profile: www.excelforum.com/member.php...oamp;userid=24783
gt; View this thread: www.excelforum.com/showthread...hreadid=502384

--

Dave Peterson


Dave

Thanks, that worked

Paul

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