Results 1 to 14 of 14
  1. #1
    New Lounger
    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 A1-A6 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.

  2. #2
    5 Star Lounger
    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.

  3. #3
    New Lounger
    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

  4. #4
    3 Star Lounger
    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

  5. #5
    3 Star Lounger
    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.

    Aladin
    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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 CTRL-SHIFT-Enter NOT just enter. Excel will add squiqqly-brackets 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

  7. #7
    New Lounger
    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)

  8. #8
    3 Star Lounger
    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>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    New Lounger
    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>

  11. #11
    New Lounger
    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>

  12. #12
    3 Star Lounger
    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:

    =6-SUM(ISERROR(FIND("a",A1:A6))*ISERROR(FIND("b",A1:A 6)))

  13. #13
    New Lounger
    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>

  14. #14
    3 Star Lounger
    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 Insert|Module.
    ( 4.) Paste the copied code in the window, entitled "...(Code)".
    ( 5.) Activate File|Close 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.
    Attached Files Attached Files
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •