|
Formula Help
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 |
Formula Help
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 |
Formula Help
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 |
Formula Help
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 |
Formula Help
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 |
| All times are GMT +1. The time now is 07:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com