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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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 !.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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.
 
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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