Page 4 of 4 FirstFirst ... 234
Results 31 to 38 of 38

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

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

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

    Quote Originally Posted by jtakw View Post
    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".
    Will it be my excel version (2016)?
    I can not enter the formula:

    "You've added too few arguments for this function"
    Regards Dante Amor

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

    You've probably typed IFERROR, use ISERROR or just copy/paste Phuoc's formula from Post # 9

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

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

    Quote Originally Posted by jtakw View Post
    You've probably typed IFERROR, use ISERROR or just copy/paste Phuoc's formula from Post # 9


    duh

    the formula works!

    (I can not copy and just paste, I have to translate the formulas, my excel is in Spanish )
    Regards Dante Amor

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

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

    Dante, are you aware of this site which translates formulas? It may help.

    https://en.excel-translator.de/translator/
    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

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

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

    Quote Originally Posted by Eric W View Post
    Dante, are you aware of this site which translates formulas? It may help.

    https://en.excel-translator.de/translator/
    Thanks Eric, I'm sure I'll consider it
    Regards Dante Amor

  6. #36
    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 Eric W View Post
    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.
    Thanks for the reply!

    Is there a way to do it, without using an array formula?

  7. #37
    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 Rick Rothstein View Post
    So a value like 12//--3/-/4 would be okay????
    Yup!

    I know, it's a very strange request!

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

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

    Not in a native Excel formula. The other way to do it would require a UDF, which needs VBA.

    To try that, open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM. In the window that opens, paste this code:

    Code:
    Public Function CountAlpha(ByVal target As Range)
    Dim x As Variant
    
        For Each x In target
            If x.Value Like "*[A-Za-z]*" Then CountAlpha = CountAlpha + 1
        Next x
    End Function
    Press Alt-Q to close the editor. Now enter the formula as:

    =CountAlpha(A1:A9)

    Let us know if that helps.
    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

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
  •