Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
I am trying to count the number of cells in a row that contain one or more of
three letters? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
I'm sure there are other and probably better solutions but this will work
=SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Won't that double count if the cell contains both a and x?
-- David Biddulph "Gary Mc" wrote in message ... I'm sure there are other and probably better solutions but this will work =SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
My bad, you are absolutely correct. I apologize for the error!
"David Biddulph" wrote: Won't that double count if the cell contains both a and x? -- David Biddulph "Gary Mc" wrote in message ... I'm sure there are other and probably better solutions but this will work =SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Give this array-entered** formula a try...
=SUM(IF(ISNUMBER(SEARCH("a",A1:A100)),1,IF(ISNUMBE R(SEARCH("b",A1:A100)),1,IF(ISNUMBER(SEARCH("c",A1 :A100)),1,0)))) **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. Note: Change the "a", "b", and "c" to the letters you want to find (keep the letters in quotes when you do). -- Rick (MVP - Excel) "Gasbag" wrote in message ... I am trying to count the number of cells in a row that contain one or more of three letters? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Someone must be able to do better than this
call with =CountChar(B2:E2,"a","b","c") Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As String) As Long For Each c In rng If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1 Next End Function Mike "Gary Mc" wrote: My bad, you are absolutely correct. I apologize for the error! "David Biddulph" wrote: Won't that double count if the cell contains both a and x? -- David Biddulph "Gary Mc" wrote in message ... I'm sure there are other and probably better solutions but this will work =SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Of course, you said "in a row". Try this array-entered formula instead...
=SUM(IF(ISNUMBER(SEARCH("a",1:1)),1,IF(ISNUMBER(SE ARCH("b",1:1)),1,IF(ISNUMBER(SEARCH("c"1:1)),1,0)) )) **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. The 1:1 in each part of the formula means Row 1... change them all (there are 3 of them) to the row you are interested in (for example 4:4 for Row 4). And, of course, still change the individual "a", "b", and "c" letters to the letters you want to look for. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this array-entered** formula a try... =SUM(IF(ISNUMBER(SEARCH("a",A1:A100)),1,IF(ISNUMBE R(SEARCH("b",A1:A100)),1,IF(ISNUMBER(SEARCH("c",A1 :A100)),1,0)))) **Commit this formula using Ctrl+Shift+Enter, not just Enter by itself. Note: Change the "a", "b", and "c" to the letters you want to find (keep the letters in quotes when you do). -- Rick (MVP - Excel) "Gasbag" wrote in message ... I am trying to count the number of cells in a row that contain one or more of three letters? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
You can simplify your function like this...
Function CountChr(Rng As Range, C1 As String, _ C2 As String, C3 As String) As Long Dim C As Range For Each C In Rng If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1 Next End Function Note I shortened the name of your function and some of your argument names in order to prevent newsreaders from word-wrapping the long If-Then statement. -- Rick (MVP - Excel) "Mike H" wrote in message ... Someone must be able to do better than this call with =CountChar(B2:E2,"a","b","c") Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As String) As Long For Each c In rng If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1 Next End Function Mike "Gary Mc" wrote: My bad, you are absolutely correct. I apologize for the error! "David Biddulph" wrote: Won't that double count if the cell contains both a and x? -- David Biddulph "Gary Mc" wrote in message ... I'm sure there are other and probably better solutions but this will work =SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Thanks rick,
I continually miss the option of putting values in an array and using Like. Mike "Rick Rothstein" wrote: You can simplify your function like this... Function CountChr(Rng As Range, C1 As String, _ C2 As String, C3 As String) As Long Dim C As Range For Each C In Rng If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1 Next End Function Note I shortened the name of your function and some of your argument names in order to prevent newsreaders from word-wrapping the long If-Then statement. -- Rick (MVP - Excel) "Mike H" wrote in message ... Someone must be able to do better than this call with =CountChar(B2:E2,"a","b","c") Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As String) As Long For Each c In rng If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1 Next End Function Mike "Gary Mc" wrote: My bad, you are absolutely correct. I apologize for the error! "David Biddulph" wrote: Won't that double count if the cell contains both a and x? -- David Biddulph "Gary Mc" wrote in message ... I'm sure there are other and probably better solutions but this will work =SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
I forgot the upper/lower case stuff. Here is a more general approach that
will allow the user to look for more (or less) than three characters and which takes care of the letter casing problem as well... Function CountChr(Rng As Range, ParamArray Char()) As Long Dim X As Long Dim C As Range Dim Chars As String For X = LBound(Char) To UBound(Char) Chars = Chars & UCase(Char(X)) Next For Each C In Rng If UCase(C.Value) Like "*[" & Chars & "]*" Then CountChar = CountChar + 1 Next End Function Now the user can do this... =CountChr(A1:Z1,"a","b") or this... =CountChr(A1:Z1,"a","b","c","d","e") etc. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can simplify your function like this... Function CountChr(Rng As Range, C1 As String, _ C2 As String, C3 As String) As Long Dim C As Range For Each C In Rng If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1 Next End Function Note I shortened the name of your function and some of your argument names in order to prevent newsreaders from word-wrapping the long If-Then statement. -- Rick (MVP - Excel) "Mike H" wrote in message ... Someone must be able to do better than this call with =CountChar(B2:E2,"a","b","c") Function CountChar(rng As Range, ch1 As String, ch2 As String, ch3 As String) As Long For Each c In rng If InStr(UCase(c.Value), UCase(ch1)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch2)) Then CountChar = CountChar + 1 If InStr(UCase(c.Value), UCase(ch3)) Then CountChar = CountChar + 1 Next End Function Mike "Gary Mc" wrote: My bad, you are absolutely correct. I apologize for the error! "David Biddulph" wrote: Won't that double count if the cell contains both a and x? -- David Biddulph "Gary Mc" wrote in message ... I'm sure there are other and probably better solutions but this will work =SUM(COUNTIF(B2:E2,"*a*"),COUNTIF(B2:E2,"*x*")) Hope this is helpful GMc "Gasbag" wrote: I am trying to count the number of cells in a row that contain one or more of three letters? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
On Sun, 7 Dec 2008 05:00:05 -0800, Gasbag
wrote: I am trying to count the number of cells in a row that contain one or more of three letters? Here is an alternative to already presented solution. This one is not an array formula: =SUMPRODUCT(1-ISERROR(FIND("a",A1:J1))*ISERROR(FIND("b",A1:J1))* ISERROR(FIND("c",A1:J1))) a,b,c to be replaced with your specific letters. A1:J1 to be adjusted to cover you width of your data row. Note that FIND is case sensitive so if you want to distinguish letter "a" from letter "A" in your data FIND it is a better choice than SEARCH which is not case sensitive. Hope this helps / Lars-Åke |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Gasbag wrote...
I am trying to count the number of cells in a row that contain one or more of three letters? Not particularly general, but the following may be the shortest formula that would do this. =COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(A1:P1,{"a";"g";"m"},"")=A1:P1)) <3)) This doesn't have to be entered as an array formula. You could make it more general. Define a name (I'll use X) referring to either a constant array of the letters sought or to a 1-column by multiple row range and use an array formula like =COUNT(1/(MMULT(TRANSPOSE(CODE(X)^0),--(SUBSTITUTE(A1:P1,X,"")=A1:P1)) <COUNTA(X))) for a more general approach. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
"Rick Rothstein" wrote...
You can simplify your function like this... Function CountChr(Rng As Range, C1 As String, _ * * * * * * * * * *C2 As String, C3 As String) As Long * Dim C As Range * For Each C In Rng * * If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1 * Next End Function .... Or make it general. Like Function foo(a As Variant, p As String) As Double Dim x As Variant If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a) For Each x In a If x Like p Then foo = foo + 1 Next x End Function which could be used in formulas like =foo(A1:P1,"*[agm]*") This would allow counting any valid LIKE pattern. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
As written, your formula is case-sensitive. If the OP requires a
case-insensitive solution, perhaps this modification will do... =COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A1:P1),{"a";"g";"m"},"")=A1:P1)) <3)) -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... Gasbag wrote... I am trying to count the number of cells in a row that contain one or more of three letters? Not particularly general, but the following may be the shortest formula that would do this. =COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(A1:P1,{"a";"g";"m"},"")=A1:P1)) <3)) This doesn't have to be entered as an array formula. You could make it more general. Define a name (I'll use X) referring to either a constant array of the letters sought or to a 1-column by multiple row range and use an array formula like =COUNT(1/(MMULT(TRANSPOSE(CODE(X)^0),--(SUBSTITUTE(A1:P1,X,"")=A1:P1)) <COUNTA(X))) for a more general approach. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
I like this idea better than my paramarray suggestion; however, I would
change your function slightly so the user would not have to know the syntax of the Like operator... Function Foo(A As Variant, P As String) As Double Dim X As Variant If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A) For Each X In A If X Like "*[" & P & "]*" Then Foo = Foo + 1 Next X End Function With this variation, all the user has to do is call the function like this... =Foo(A1:P1,"agm") -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... "Rick Rothstein" wrote... You can simplify your function like this... Function CountChr(Rng As Range, C1 As String, _ C2 As String, C3 As String) As Long Dim C As Range For Each C In Rng If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1 Next End Function .... Or make it general. Like Function foo(a As Variant, p As String) As Double Dim x As Variant If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a) For Each x In a If x Like p Then foo = foo + 1 Next x End Function which could be used in formulas like =foo(A1:P1,"*[agm]*") This would allow counting any valid LIKE pattern. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
I just noticed that I forgot to provide for case insensitivity. How about we
make that optional (and change the function and arguments to something more meaningful)... Function Foo(A As Variant, ByVal P As String, _ Optional C As Boolean) As Double Dim X As Variant If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A) If C Then P = LCase(P) & UCase(P) For Each X In A If X Like "*[" & P & "]*" Then Foo = Foo + 1 Next X End Function As set up, the function is case sensitive; if the user wants the count to be case insensitive, they just need to include True for the 3rd argument. For example... =Foo(A1:P1,"agm",TRUE) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I like this idea better than my paramarray suggestion; however, I would change your function slightly so the user would not have to know the syntax of the Like operator... Function Foo(A As Variant, P As String) As Double Dim X As Variant If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A) For Each X In A If X Like "*[" & P & "]*" Then Foo = Foo + 1 Next X End Function With this variation, all the user has to do is call the function like this... =Foo(A1:P1,"agm") -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... "Rick Rothstein" wrote... You can simplify your function like this... Function CountChr(Rng As Range, C1 As String, _ C2 As String, C3 As String) As Long Dim C As Range For Each C In Rng If C.Value Like "*[" & C1 & C2 & C3 & "]*" Then CountChr = CountChr + 1 Next End Function ... Or make it general. Like Function foo(a As Variant, p As String) As Double Dim x As Variant If Not TypeOf a Is Range And Not IsArray(a) Then a = Array(a) For Each x In a If x Like p Then foo = foo + 1 Next x End Function which could be used in formulas like =foo(A1:P1,"*[agm]*") This would allow counting any valid LIKE pattern. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
Hi,
You can also try something like this. In a separate range , day D10:D12, type the 3 letters. Now suppose your entries are in A1:A50. in cell B1, type this array formula (Ctrl+Shift+Enter) and copy down =1*OR(ISNUMBER(SEARCH($D$10:$D$12,A1,1))) Now simply sum up column B. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Gasbag" wrote in message ... I am trying to count the number of cells in a row that contain one or more of three letters? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
It also counts cells that are numeric:
gmail...10...AAA...<empty...3M If there might be both text and numbers then you need to add a test for text: =SUMPRODUCT(--(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A1:P1),{"a";"g";"m"},"")=A1:P1)) <3),--(ISTEXT(A1:P1))) If there are errors in the range that'll return the error and the COUNT version(s) will return 0. gmail...10...AAA...#N/A...3M -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... As written, your formula is case-sensitive. If the OP requires a case-insensitive solution, perhaps this modification will do... =COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A1:P1),{"a";"g";"m"},"")=A1:P1)) <3)) -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... Gasbag wrote... I am trying to count the number of cells in a row that contain one or more of three letters? Not particularly general, but the following may be the shortest formula that would do this. =COUNT(1/(MMULT({1,1,1},--(SUBSTITUTE(A1:P1,{"a";"g";"m"},"")=A1:P1)) <3)) This doesn't have to be entered as an array formula. You could make it more general. Define a name (I'll use X) referring to either a constant array of the letters sought or to a 1-column by multiple row range and use an array formula like =COUNT(1/(MMULT(TRANSPOSE(CODE(X)^0),--(SUBSTITUTE(A1:P1,X,"")=A1:P1)) <COUNTA(X))) for a more general approach. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
"Rick Rothstein" wrote...
I like this idea better than my paramarray suggestion; however, I would change your function slightly so the user would not have to know the syntax of the Like operator... .... IMO, better for users to know the syntax for LIKE operator patterns since that would allow significantly greater flexibility and functionality than simply searching for alternative single characters. |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
"T. Valko" wrote...
It also counts cells that are numeric: gmail...10...AAA...<empty...3M If there might be both text and numbers then you need to add a test for text: =SUMPRODUCT(--(MMULT({1,1,1},--(SUBSTITUTE(LOWER(A1:P1), {"a";"g";"m"},"")=A1:P1))<3),--(ISTEXT(A1:P1))) .... No, better to use ISNUMBER(SEARCH(...)). =COUNT(1/MMULT({1,1,1},--ISNUMBER(SEARCH({"a";"g";"m"},A1:P1)))) |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Count Functions
True, but Like is a VB programming concept and my guess is that most Excel
users are not VB literate, so I would think many (if not most) of the users would not be familiar with Like's operator patterns. However, I think we can cater to both types of users with something like this (off the top of the head code) maybe... Function Foo(A As Variant, ByVal P As String, _ Optional C As Boolean) As Double Dim X As Variant If Not P Like "*[*?#[]*" Then P = "*[" & P & "]*" If Not TypeOf A Is Range And Not IsArray(A) Then A = Array(A) For Each X In A If C Then If UCase(X) Like UCase(P) Then Foo = Foo + 1 ElseIf X Like P Then Foo = Foo + 1 End If Next X End Function If the user uses an asterisk, question mark, hash mark or left square bracket in his/her pattern string, then the code assumes the user has constructed a Like pattern string and passes it through as it; if none of those characters are present, then the code assumes the user is looking for a simple letter match. The optional C argument still optionally allows for case sensitive or case insensitive searches. -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... "Rick Rothstein" wrote... I like this idea better than my paramarray suggestion; however, I would change your function slightly so the user would not have to know the syntax of the Like operator... ... IMO, better for users to know the syntax for LIKE operator patterns since that would allow significantly greater flexibility and functionality than simply searching for alternative single characters. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Functions | Excel Discussion (Misc queries) | |||
Count Functions | Excel Worksheet Functions | |||
Fun with COUNT and AND functions. | Excel Worksheet Functions | |||
functions to count Yes & No | Excel Worksheet Functions | |||
Excel IF and COUNT functions | Excel Worksheet Functions |