# Thread: counting cells with certain characters (2001)

1. ## 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. ## 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. ## 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. ## 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. ## 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.

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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)".

You're done.

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.

#### Posting Permissions

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