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!

 

[JoeMo]

Maybe:

Excel Workbook
	A	B	C
1	3
2			3
3	12345
4	A4567
5	3546
6	23A78
7	765
8	2A6A8
Sheet1

 

[Aitch]

I tried that - but forgot the "" lol... thank you!

Is there a way to count cells which contain a number of different letters, or even any kind of alphabetical character?

 

[JoeMo]

I tried that - but forgot the "" lol... thank you!

Is there a way to count cells which contain a number of different letters, or even any kind of alphabetical character?

You are welcome - thanks for the reply. Can you be a bit more descriptive on your new question and maybe provide a few examples of what you want to do?

 

[Aitch]

Yup!

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]123456
[/TD]
[/TR]
[TR]
[TD]12A34B[/TD]
[/TR]
[TR]
[TD]1CC345[/TD]
[/TR]
[TR]
[TD]1ABCD5[/TD]
[/TR]
[TR]
[TD]111222[/TD]
[/TR]
[TR]
[TD]ABCDEF[/TD]
[/TR]
[TR]
[TD]123123[/TD]
[/TR]
[TR]
[TD]123EF6[/TD]
[/TR]
</tbody>[/TABLE]


So in the column, there are 5 cells which contain letters - is there a way to use COUNTIF to find this value?

 

[Rick Rothstein]

Assuming all of the cells are not formatted as Text...

=COUNTA(A2:A9)-COUNT(A2:A9)

 

[XOR LX]

Assuming all of the cells are not formatted as Text...

=COUNTA(A2:A9)-COUNT(A2:A9)

If your assumption is correct then we can also use:

=COUNTIF(A2:A9,"?*")

though note that your count will also include any null strings ("") within the range, should there be any.

Regards

 

[Aitch]

Yes, the formatting is Text... dangit!

Is it still possible to do somehow?

 

[Phuoc]

Try

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

 

[Aitch]

Try

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

Tried but doesn't seem to work... thank you anyway!