Results 1 to 14 of 14

20020727, 02:10 #1
 Join Date
 May 2002
 Location
 Canada
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
counting cells with certain characters (2001)
Subject: counting cells with certain characters
Hi,
I am trying to count the number of cells that contain any one of several specified characters. For example, cells A1A6 contain the following text strings:
A1 wa
A2 wabp
A3 wb
A4 cp
A5 a
A6 wp
About 2 months ago I received wonderful help from this board telling me that I could, for example, count the number of cells which contained the letter, "a" using the formula
=COUNTIF(A1:A6,"*a*")
The formula returned the number, 3, which is exactly what I wanted. Many thanks to Aladin Akyurek!
Now I would like to count the number of cells which contain any one of several characters. In a test case, I naively tried the following formula to count the number of cells which contained either a or b.
=COUNTIF(A1:A6,OR("*a*","*b*"))
The above formula returned the number, 0, instead of the desired number, 4. I tried a few other variations on this theme but never came up with a workable formula. I suspect that my arguments within the OR function are incomplete but I'm not sure what to do to make them complete.
Any help would be much appreciated.

20020727, 04:42 #2
 Join Date
 Feb 2001
 Location
 Dallas plus 20 miles or so, Texas, USA
 Posts
 876
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Hi Dex,
Per your above specific problem, "=COUNTIF(A1:A6,"*a*")+COUNTIF(A1:A6,"*b*")" ought to do the trick. If you can tell us what your end goal with the cells is, there might be a more direct solution to the end you seek...i.e. searching for a text string, sorting another column from the results, etc.

20020727, 05:42 #3
 Join Date
 May 2002
 Location
 Canada
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Mike,
Thank you for the reply. In my example, your formula returns a result of 5 because it counts cell A2 twice. The correct (desired) result is 4, as I mentioned in my original post. I want each cell that contains a or b or both a and b to be counted exactly once.
Regarding your question about the goal, the letters in the cells refer to attendance at various meetings. In the example I posted, there are 5 possible meetings to attend, signified by the letters a, b, c, p, w. Two people attended meeting a only. One attended meeting b only. One attended both a and b. Other meetings were also attended. In this example I am interested in knowing who attended at least one of the meetings, a or b. Four people attended at least one of these two meetings. In my actual spreadsheet there are more people and more possible meetings and there are other subsets of meetings I will want to examine from time to time. Once I solve the problem for this smaller example, I should be able to extend it to the larger spreadsheet.
Dex

20020727, 06:55 #4
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
This formula shoud work but is a bit clunky!
=COUNTIF(A1:A10,"*a*")+COUNTIF(A1:A10,"*b*")COUNTIF(A1:A10,"*a*B*")COUNTIF(A1:A10,"*b*a*")
If you want to extend this past 2 choices then it will get rally clunky <img src=/S/meltdown.gif border=0 alt=meltdown width=15 height=15>
Hopefully someone else will come up with a more elagant solution for you
Peter

20020727, 07:27 #5
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Let B1:C1 house "a" and "b", respectively.
Then use:
=SUMPRODUCT((ISNUMBER(SEARCH(B1,SUBSTITUTE(A1:A6,C 1,B1)))+0))
to produce the desired count.
What if there are three letters to consider, say, "a", "b", and "p" in B11?
Due to the use of SUBSTITUTE with its application semantics (that is, how this function works), we'll need:
=SUMPRODUCT((ISNUMBER(SEARCH(B1,SUBSTITUTE(SUBSTIT UTE(A1:A6,C1,B1),D1,B1))))+0)
As can be seen from the above example, the 2nd to Nth criterion letters are replaced with the 1st criterion letter and the result strings are then fed to SEARCH.
What I'm trying to say is that there is a limit to the number of criterion letters that we can use, due to how SUBSTITUTE operates.
AladinMicrosoft MVP  Excel

20020727, 19:51 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: counting cells with certain characters (2001)
Try the ARRAY Formula:
=COUNT(IF((NOT(ISERROR(FIND("a",A1:A6))))+(NOT(ISE RROR(FIND("b",A1:A6)))),1))
Remember: to enter an array formula, in edit mode hit CTRLSHIFTEnter NOT just enter. Excel will add squiqqlybrackets around it ({})
If you want both A and B containing use:
=COUNT(IF((NOT(ISERROR(FIND("a",A1:A6))))*(NOT(ISE RROR(FIND("b",A1:A6)))),1))
Steve

20020728, 03:15 #7
 Join Date
 May 2002
 Location
 Canada
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Well, it seems that my problem was more complex than I thought. I wasn't even scratching the surface with my feeble attempt to figure this one out.
Much thanks to you all for this wonderful help. I learned something from each of you and archived your formulas in my test spreadsheet for future help. The formulas from sdckapr (Steve), Aladin, and bat17 (Peter) all produced the desired result in my test spreadsheet with a subset of 2. The formula from Steve seemed to be the easiest to expand to larger subsets and is now functioning well in my current spreadsheet with a subset of 4.
Thank you all again,
Poindexter (Steve)

20020728, 10:52 #8
 Join Date
 Jun 2001
 Location
 Maidstone, Kent, England
 Posts
 398
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Said mine was Clunky <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20020728, 13:08 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: counting cells with certain characters (2001)
FYI,
You can also calculate MINIF, MAXIF, AVERAGEIF, etc using this type of technique with ARRAY formulas. Use Additon for ORs and Multiply for ANDs. You can make all sorts of combinations with parantheses to make it as complex as you want.
Steve

20020729, 02:08 #10
 Join Date
 May 2002
 Location
 Canada
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
You said it, not me. Thanks for your help anyway. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20020729, 02:12 #11
 Join Date
 May 2002
 Location
 Canada
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Steve,
Thanks for the additional info. I am getting quite an education.
Steve
<img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20020729, 16:28 #12
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: counting cells with certain characters (2001)
Apologies if this is part of anybody else's solution, but an alternative approach might be to count the "misses" rather than the hits and then subtract them from the total.
The (array) formula would be:
=6SUM(ISERROR(FIND("a",A1:A6))*ISERROR(FIND("b",A1:A 6)))

20020729, 19:27 #13
 Join Date
 May 2002
 Location
 Canada
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Thank you Collin. Similar in approach to solution given by sdckapr (Steve) but more compact.
Steve <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

20020729, 19:58 #14
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: counting cells with certain characters (2001)
Steve,
I can imagine stringing together a bunch of NOTs are easier than nesting a bunch of SUBSTITUTES... [img]/forums/images/smilies/smile.gif[/img]
Here another, I hope, much easier solution:
=SUMPRODUCT((ISNUMBER(SEARCH("@",translate(A1:A6,B 1&B2,REPT("@",COUNTA(B1:B2))))))+0)
where A1:A6 houses your sample data, B1 "a", and B2 "b".
When you expand the subset, say, from B1:B2 to B1:B3, you just adapt the B1&B2 and B1:B2 bits: B1&B2&B3 and B1:B3, respectively.
The 'translate' is a UDF that you must add to your WB:
Function Translate(ByVal trStr As Range, frStr As String, toStr As String) As Variant
'
' Juan Pablo G.
' 7/29/2002
'
Dim iRow As Integer
Dim iCol As Integer
Dim j As Integer
Dim Ar As Variant
If Len(frStr) <> Len(toStr) Then
Translate = CVErr(2036)
Exit Function
End If
If trStr.Count > 1 Then
Ar = trStr.Value
For iRow = LBound(Ar, 1) To UBound(Ar, 1)
For iCol = LBound(Ar, 2) To UBound(Ar, 2)
For j = 1 To Len(toStr)
Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), Mid(frStr, j, 1), Mid(toStr, j, 1))
Next j
Next iCol
Next iRow
Else
Ar = trStr.Value
For iRow = 1 To Len(toStr)
Ar = Application.Substitute(Ar, Mid(frStr, iRow, 1), Mid(toStr, iRow, 1))
Next iRow
End If
Translate = Ar
End Function
To add the above code to the target WB:
( 1.) Open it (close all other WB's).
( 2.) Copy the code above.
( 3.) Activate InsertModule.
( 4.) Paste the copied code in the window, entitled "...(Code)".
( 5.) Activate FileClose and Return to Microsoft Excel.
You're done.
Aladin
Addendum. I'm attaching a WB which also contains an additional UDF, MSUBSTITUTE (due to Juan Pablo G.). I also verified the UDFs are fully functional on the Mac.
The formula with MSUBSTITUTE is:
=SUMPRODUCT((ISNUMBER(SEARCH("@",msubstitute(A1:A6 ,B1&B2,REPT("@",COUNTA(B1:B2))))))+0)
where B1:B2 houses the condition letters.Microsoft MVP  Excel