Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 38

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

  1. #11
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,223
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

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

    This
    =COUNTIF(A2:A9,">0")

    Or this
    =SUMPRODUCT(COUNTIF(A2:A9,">0"))

    ---

    Or array formula:

    {=COUNT(IF(VALUE(A2:A9)>0,1,0))}

    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Last edited by DanteAmor; May 13th, 2019 at 07:05 PM.
    Regards Dante Amor

  2. #12
    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...

    Doesn't work either - is this impossible to do?

  3. #13
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,223
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by Aitch View Post
    Doesn't work either - is this impossible to do?
    It works with the data that you put.
    Or do you have other information?


    Hoja22

     AB
    1  
    2123453
    312A34B3
    41CC3453
    51ABCD5 
    6111222 
    7ABCDEF 
    8123123 
    9123EF6 

    Formeln der Tabelle
    ZelleFormel
    B2=COUNTIF(A2:A9,">0")
    B3=SUMPRODUCT(COUNTIF(A2:A9,">0"))
    B4{=COUNT(IF(VALUE(A2:A9)>0,1,0))}
    Regards Dante Amor

  4. #14
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    @DanteAmor

    Your formula is OK BUT....

    The OP want exact opposite...
    counting cells which contain a specific letter...
    Last edited by Tom.Jones; May 15th, 2019 at 06:38 PM.

  5. #15
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,223
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by Tom.Jones View Post
    @DanteAmor

    Your formula is OK BUT....

    The OP want exact opposite...



    Try this

    =COUNTA(A2:A9)-COUNTIF(A2:A9,">0")
    Regards Dante Amor

  6. #16
    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...

    Still not working!

    I need the value to be at the top in cell A1... and to count the cells which contain non-numerical letters.

    It is formatted in plain text, which I think is the cause of the problem... dangit!

  7. #17
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,

    This formula is working:

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


    it does not matter how the range is formatted.

  8. #18
    Board Regular
    Join Date
    Feb 2013
    Posts
    151
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Hi,
    just happened across this post in a browsing moment. Thought I'd try it out.
    That formula by Phuoc works perfectly. Except that for the dataset you have provided it would need to be A2:A11 instead of A2:A9.

  9. #19
    Board Regular
    Join Date
    Sep 2011
    Posts
    180
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by sparky2205 View Post
    Hi,
    just happened across this post in a browsing moment. Thought I'd try it out.
    That formula by Phuoc works perfectly. Except that for the dataset you have provided it would need to be A2:A11 instead of A2:A9.
    Hi,

    What is the logic, that range is A2: A11 and not A2: A9 ????

  10. #20
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,223
    Post Thanks / Like
    Mentioned
    69 Post(s)
    Tagged
    14 Thread(s)

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

    Array formula. To accept press shift+control+enter

    {=SUM(IF(ISNUMBER(A2:A9+0),0,1))}

    If entered correctly, excel will surround with curly braces {}.
    Note: do not try and enter the {} manually yourself.
    Regards Dante Amor

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
  •