# Thread: count cells with specified character (Office 20

1. ## count cells with specified character (Office 20

Hi,

This is my first time to use this board so I hope I post this properly. In former years I did computer programming in such languages as FORTRAN, BASIC, and PASCAL. Now I'm trying to learn Excel and things I would have thought simple are baffling me.

Here's my problem:

I have a column of cells, each of which contains a short text string. I want to count how many cells have a given character and display this number in another cell at the top of the column as shown below:

------A--------------B
---------------------------------
1--total w---------6

2--total a---------3

3--total b---------2

4--total c----------2

5--total p----------3

---------------------------------

6--Tom---------wa

7--Dick----------wabp

8--Harry---------wb

9--Suzie---------wcp

10--Beth---------a

11--Jane--------wp

12--Sam---------wc

I want cells B1 through B5 to contain the formulas to count the number of cells (within the range B6-B12) that contain a specified character. But I haven't been able to design a formula that works. I thought the COUNTIF function would work in conjunction with one of the text functions, but I'm stuck. Am I going beyond what can be done with Excel or am I overlooking something?

2. ## Re: count cells with specified character (Office 20

Try Bob's site Bob Umlas.
Look at the heading "Counting occurrences of substrings in a range of cells"

3. ## Re: count cells with specified character (Office 20

Try:

=SUMPRODUCT((COUNTIF(B6:B12,"*w*")))

or

=SUMPRODUCT((COUNTIF(\$B\$6:\$B\$12,"*"&A1&"*")))

where A1 houses just the target letter (e.g., "w" without double quotes)

4. ## Re: count cells with specified character (Office 2

The formula, =SUMPRODUCT((COUNTIF(B6:B12,"*w*"))), works like a charm. Exactly what I needed.

Poindexter

5. ## Re: count cells with specified character (Office 2

Thanks Kieran,

I went to the Bob Umlas site and it looks like it will be a good resource for me as I continue my work.

Poindexter.

#### Posting Permissions

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