Register
Log In
Unanswered
Home
June 25, 2016
Offline
Hy Mynda,
I have data in column A like from A1:A9
12 A1
13 A2
14 A3
- A4
$ A5
# A6
@A7
A8
20 A9
what formula to count blank nd special characters like $,#,&,@...?
regards,
July 16, 2010
Offline
Hi Rathanak,
You can use this formula to count all cells that don't contain numbers, it's easier than trying to handle all sorts of special characters:
=SUMPRODUCT(--NOT(ISNUMBER(A1:A9)))
Mynda
June 25, 2016
Offline
Thanks Mynda
New Member
December 21, 2016
Offline
A bit shorter formula would be:
=COUNTA(A1:A9)-COUNTIF(A1:A9,"")
Micky
July 16, 2010
Offline
Hi Micky,
Great to see you having a go, however that formula counts everything except the blank cell. Rathanak wants to also count the special characters.
If you wanted to make mine shorter you could always use:
=SUM(--NOT(ISNUMBER(A1:A9)))
And enter with CTRL+SHIFT+ENTER.
But I don't recommend array formulas unless you have to use them.
Mynda
Dashboards
June 25, 2016
Offline
Hi Mynda
When I looked at rathanak's question, I assumed that numbers and alphabets are to be excluded.
=SUMPRODUCT(--NOT(ISNUMBER(A1:A9))) will exclude numbers but what about alphabets and space/s in a cell (not blank cell) that are not special characters?
Sunny
Dashboards
June 25, 2016
Offline
Attached is the formula that I was working on but it is rather long when combined.
June 25, 2016
Offline
Thanks Sunny, awesome
I so glad to learn it from you
Once again thanks Mynda
July 16, 2010
Offline
In that case I'd probably cheat and use a formula like this:
=COUNTBLANK(B1:B9)-COUNT(A1:A9)
Assuming column B is empty.
In other words, count how many cells in the range A1:A9 don't contain numbers.
Mynda
June 25, 2016
Offline
Glad to learn
New Member
December 21, 2016
Offline
Mynda Treacy said
Hi Micky,Great to see you having a go, however that formula counts everything except the blank cell. Rathanak wants to also count the special characters.
If you wanted to make mine shorter you could always use:
=SUM(--NOT(ISNUMBER(A1:A9)))
And enter with CTRL+SHIFT+ENTER.
But I don't recommend array formulas unless you have to use them.
Mynda
Hi,
Yes, I noticed some small TIPOs in my previous suggested formula.
As per the question - I assumed the OP has a mixed list of Numbers/Special Characters/empty cells.
For that I would try another formula, such as: =COUNT(A1:A9)+COUNTIF(A1:A9,"")
Micky
Most Users Ever Online: 57
Currently Online: azri adrin, Mark Lund, Rudi
18 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
