Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thanks for any help you can give me.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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)
    Microsoft MVP - Excel

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count cells with specified character (Office 2

    Thanks Aladin,

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

    Poindexter

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •