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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,182
Office Version
2007
Platform
Windows
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:

Some videos you may like

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.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,182
Office Version
2007
Platform
Windows
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>
 

Aitch

Board Regular
Joined
Jan 27, 2019
Messages
61
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
Joined
Sep 20, 2011
Messages
240
Hi,

This formula is working:

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


it does not matter how the range is formatted.
 

sparky2205

Board Regular
Joined
Feb 6, 2013
Messages
173
Office Version
365, 2016
Platform
Windows
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
Joined
Sep 20, 2011
Messages
240
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
Joined
Dec 3, 2018
Messages
10,182
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,089,200
Messages
5,406,789
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top