count cells with specified character (Office 20

Topic

New Reply

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?

Thanks for any help you can give me.

Reply | Quote

Replies

Try Bob’s site Bob Umlas.
Look at the heading “Counting occurrences of substrings in a range of cells”

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

Thanks Aladin,

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

Poindexter

Reply | Quote