# Thread: COUNTIF question - counting cells which contain a specific letter... Thanks:  24 Post #5278599 (1)Post #5272831 (1)Post #5279149 (1)Post #5278720 (1)Post #5277730 (1) Likes: 0

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

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 {}.

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

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

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

Originally Posted by Aitch
Doesn't work either - is this impossible to do?
It works with the data that you put.
Or do you have other information?

Hoja22

 A B 1 2 12345 3 3 12A34B 3 4 1CC345 3 5 1ABCD5 6 111222 7 ABCDEF 8 123123 9 123EF6

Formeln der Tabelle
 Zelle Formel B2 =COUNTIF(A2:A9,">0") B3 =SUMPRODUCT(COUNTIF(A2:A9,">0")) B4 {=COUNT(IF(VALUE(A2:A9)>0,1,0))}

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

@DanteAmor

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

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

Originally Posted by Tom.Jones
@DanteAmor

The OP want exact opposite...

Try this

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

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

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!

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

Hi,

This formula is working:

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

it does not matter how the range is formatted.

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

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.

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

Originally Posted by 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.
Hi,

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

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

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.