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

Thanks Mynda

A bit shorter formula would be:

=COUNTA(A1:A9)-COUNTIF(A1:A9,"")

Micky

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 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

Attached is the formula that I was working on but it is rather long when combined.

Thanks Sunny, awesome

I so glad to learn it from you

Once again thanks Mynda

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

Glad to learn

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