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!

 

[DanteAmor]

This
=COUNTIF(A2:A9,">0")

Or this
=SUMPRODUCT(COUNTIF(A2:A9,">0"))

---

Or array formula:

{=COUNT(IF(VALUE(A2:A9)>0,1,0))}

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Aitch]

Doesn't work either - is this impossible to do?

 

[DanteAmor]

Doesn't work either - is this impossible to do?

It works with the data that you put.
Or do you have other information?


<b>Hoja22</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">12345</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >12A34B</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >1CC345</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >1ABCD5</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">111222</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >ABCDEF</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">123123</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >123EF6</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B2</td><td >=COUNTIF(A2:A9,">0")</td></tr><tr><td >B3</td><td >=SUMPRODUCT(COUNTIF(A2:A9,">0"))</td></tr><tr><td >B4</td><td >{=COUNT(IF(VALUE(A2:A9)>0,1,0))}</td></tr></table></td></tr></table>

 

[Tom.Jones]

@DanteAmor

Your formula is OK BUT....

The OP want exact opposite...

counting cells which contain a specific letter...

 

[DanteAmor]

@DanteAmor

Your formula is OK BUT....

The OP want exact opposite...

Try this

=COUNTA(A2:A9)-COUNTIF(A2:A9,">0")

 

[Aitch]

Still not working!

I need the value to be at the top in cell A1... and to count the cells which contain non-numerical letters.

It is formatted in plain text, which I think is the cause of the problem... dangit!

 

[Tom.Jones]

Hi,

This formula is working:

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


it does not matter how the range is formatted.

 

[sparky2205]

Hi,
just happened across this post in a browsing moment. Thought I'd try it out.
That formula by Phuoc works perfectly. Except that for the dataset you have provided it would need to be A2:A11 instead of A2:A9.

 

[Tom.Jones]

Hi,
just happened across this post in a browsing moment. Thought I'd try it out.
That formula by Phuoc works perfectly. Except that for the dataset you have provided it would need to be A2:A11 instead of A2:A9.

Hi,

What is the logic, that range is A2: A11 and not A2: A9 ????

 

[DanteAmor]

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.