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

#### DanteAmor

##### Well-known Member
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 {}.

Last edited:

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Aitch

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

#### DanteAmor

##### Well-known Member
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

##### Board Regular
@DanteAmor

The OP want exact opposite...
counting cells which contain a specific letter...

Last edited:

#### DanteAmor

##### Well-known Member
@DanteAmor

The OP want exact opposite...

Try this

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

#### Aitch

##### Board Regular
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

##### Board Regular
Hi,

This formula is working:

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

it does not matter how the range is formatted.

#### sparky2205

##### Board Regular
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

##### Board Regular
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

##### Well-known Member
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.