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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,222
Office Version
2007
Platform
Windows
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"
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You've probably typed IFERROR, use ISERROR or just copy/paste Phuoc's formula from Post # 9
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,222
Office Version
2007
Platform
Windows
You've probably typed IFERROR, use ISERROR or just copy/paste Phuoc's formula from Post # 9


duh :banghead:

the formula works!

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

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
61
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?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,313
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,973
Messages
5,411,586
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top