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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,656
Office Version
2010
Platform
Windows
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))
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,221
Office Version
2007
Platform
Windows
Try

=SUMPRODUCT(--ISERROR(--(0&A2:A9) , missing_value_if_error ))

https://support.office.com/en-us/article/iferror-function-c526fd07-caeb-47b8-8bb6-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 !.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,656
Office Version
2010
Platform
Windows
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:

DanteAmor

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

Aitch

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

DanteAmor

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

Eric W

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,656
Office Version
2010
Platform
Windows
....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????
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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:

Watch MrExcel Video

Forum statistics

Threads
1,089,886
Messages
5,410,996
Members
403,336
Latest member
amreeves87

This Week's Hot Topics

Top