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

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
119
Office Version
  1. 2010
Platform
  1. Windows
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

<tbody>
</tbody>

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:
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"
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You've probably typed IFERROR, use ISERROR or just copy/paste Phuoc's formula from Post # 9
 
Upvote 0
You've probably typed IFERROR, use ISERROR or just copy/paste Phuoc's formula from Post # 9



duh :oops:

the formula works!

(I can not copy and just paste, I have to translate the formulas, my excel is in Spanish :()
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top