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

[Aitch]

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

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A4567[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3546[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23A78[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]765[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2A6A8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[DanteAmor]

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"

 

[jtakw]

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

 

[DanteAmor]

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 )

 

[Eric W]

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

https://en.excel-translator.de/translator/

 

[DanteAmor]

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

 

[Aitch]

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?

 

[Aitch]

So a value like 12//--3/-/4 would be okay????

Yup!

I know, it's a very strange request!

 

[Eric W]

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:

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.