Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 38

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

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

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

    Quote Originally Posted by DanteAmor View Post
    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.
    This normally-entered formula should produce the same results as your formula above...

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

  2. #22
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,218
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by Rick Rothstein View Post
    This "normally-entered" formula should produce the same results as your formula above...

    =SUMPRODUCT(1-ISNUMBER(A2:A9+0))
    It enters normal, but we know that it is an array formula.

    Thanks
    Regards Dante Amor

  3. #23
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,218
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 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) , missing_value_if_error ))

    https://support.office.com/en-us/art...6-63f3e417f611

    DescriptionReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula.
    Syntax
    IFERROR(value, value_if_error)
    The IFERROR function syntax has the following arguments:


    Value Required. The argument that is checked for an error.


    Value_if_error Required. The value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE !, #REF !, #DIV/0!, #NUM !, #NA ME?, or #NULL !.



    Regards Dante Amor

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

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

    Quote Originally Posted by DanteAmor View Post
    It enters normal, but we know that it is an array formula.
    Normally entered formulas (whether array processing underneath or not) are preferred so the user does not have to remember to use CTRL+SHIFT+ENTER if they accidentally (or on purpose) enter Edit Mode for a cell with the formula in it. To be noted from my formula, if you want to apply it to your formula, is the way in which I eliminated the need for the IF function call.
    Last edited by Rick Rothstein; May 17th, 2019 at 10:00 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

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

    Quote Originally Posted by Rick Rothstein View Post
    Normally entered formulas (whether array processing underneath or not) are preferred so the user does not have to remember to use CTRL+SHIFT+ENTER if they accidentally (or on purpose) enter Edit Mode for a cell with the formula in it. To be noted from my formula, if you want to apply it to your formula, is the way in which I eliminated the need for the IF function call.
    No doubt your formula is better and shorter; and that I appreciate because I'm still learning, but I'm not talking about that, only the fact that it's also an array formula.
    Regards Dante Amor

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

    Sorry for causing so much confusion! I think I've found the problem...

    Yes that formula does work - but I thought it didn't, because it also counts other characters like - / etc... dangit!

    Is there a way to count cells which ONLY contain letters A-Z ?

  7. #27
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,218
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

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

    Quote Originally Posted by Aitch View Post
    Sorry for causing so much confusion! I think I've found the problem...

    Yes that formula does work - but I thought it didn't, because it also counts other characters like - / etc... dangit!

    Is there a way to count cells which ONLY contain letters A-Z ?
    Which formula works?
    And you can put complete examples and the expected result.
    Regards Dante Amor

  8. #28
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,644
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

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

    Interesting thread!

    This array formula will only count cells with at least 1 letter of the alphabet, case disregarded:

    =SUM(SIGN(MMULT(IFERROR(SEARCH(CHAR(COLUMN(INDIRECT("A:Z"))+64),A2:A9),0),ROW(INDIRECT("1:26")))))

    confirmed with Control+Shift+Enter. Numbers and other characters and empty cells are not counted.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

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

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

    Quote Originally Posted by Aitch View Post
    ....because it also counts other characters like - / etc... dangit!

    Is there a way to count cells which ONLY contain letters A-Z ?
    So a value like 12//--3/-/4 would be okay????
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #30
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 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)))
    Hi,

    Regarding Post # 23...

    Just pointing out, formula provided by Phouc in Post # 9 uses ISERROR (which evaluates to TRUE/FALSE, and hence his use of the double unary -- ), Not IFERROR, so No "Value if error" is needed or even should be added, formula will end up invalid due to "too many arguments".
    Last edited by jtakw; May 17th, 2019 at 07:56 PM.

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
  •