Page 1 of 4 123 ... LastLast
Results 1 to 10 of 38

Thread: COUNTIF question - counting cells which contain a specific letter...

  1. #1
    Board Regular
    Join Date
    Jan 2019
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIF question - counting cells which contain a specific letter...

    Is there a way to use the COUNTIF function to count cells if they contain a single letter - for example below, when a cell has the letter A, the count would be 3 in the top row

    3
    12345
    A4567
    3546
    23A78
    765
    2A6A8

    I thought it would be easy using wildcards, but is doesn't seem to work... and I think having two letters in the cell makes things worse!

    Thanks for helping!
    Last edited by Aitch; May 5th, 2019 at 04:29 PM.

  2. #2
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,487
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Maybe:
    Sheet1

    ABC
    13
    2 3
    312345
    4A4567
    53546
    623A78
    7765
    82A6A8

    Spreadsheet Formulas
    CellFormula
    C2=COUNTIF(A1:A8,"*A*")


    Excel tables to the web >> Excel Jeanie HTML 4
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  3. #3
    Board Regular
    Join Date
    Jan 2019
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    I tried that - but forgot the "" lol... thank you!

    Is there a way to count cells which contain a number of different letters, or even any kind of alphabetical character?

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,487
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    8 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Quote Originally Posted by Aitch View Post
    I tried that - but forgot the "" lol... thank you!

    Is there a way to count cells which contain a number of different letters, or even any kind of alphabetical character?
    You are welcome - thanks for the reply. Can you be a bit more descriptive on your new question and maybe provide a few examples of what you want to do?
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    Board Regular
    Join Date
    Jan 2019
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Yup!

    5
    123456
    12A34B
    1CC345
    1ABCD5
    111222
    ABCDEF
    123123
    123EF6


    So in the column, there are 5 cells which contain letters - is there a way to use COUNTIF to find this value?

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Assuming all of the cells are not formatted as Text...

    =COUNTA(A2:A9)-COUNT(A2:A9)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,517
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Quote Originally Posted by Rick Rothstein View Post
    Assuming all of the cells are not formatted as Text...

    =COUNTA(A2:A9)-COUNT(A2:A9)
    If your assumption is correct then we can also use:

    =COUNTIF(A2:A9,"?*")

    though note that your count will also include any null strings ("") within the range, should there be any.

    Regards
    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Board Regular
    Join Date
    Jan 2019
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Yes, the formatting is Text... dangit!

    Is it still possible to do somehow?

  9. #9
    Board Regular
    Join Date
    Apr 2016
    Location
    Vietnam
    Posts
    132
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Try

    =SUMPRODUCT(--ISERROR(--(0&A2:A9)))

  10. #10
    Board Regular
    Join Date
    Jan 2019
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIF question - counting cells which contain a specific letter...

    Quote Originally Posted by Phuoc View Post
    Try

    =SUMPRODUCT(--ISERROR(--(0&A2:A9)))
    Tried but doesn't seem to work... thank you anyway!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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