Count if not a number
 

I have a range of cells AI7:AT7 and I need a count to see if users made an
error by entering a blank, a space, a period ect. Where numbers should have
been enterd. I have tried several count variations and could not come up with
any thing.

Count if not a number
 

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come up
with
any thing.

Count if not a number
 

That is almost what I want, but I need it to treat the blanks the same as it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come up
with
any thing.

Count if not a number
 

=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test of
an IF() formula.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the same as
it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made
an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come
up
with
any thing.

Count if not a number
 

Slightly shorter:

=SUM(COUNT(AI7:AT7),--ISBLANK(AI7:AT7))=SUM(COUNTA(AI7:AT7),--ISBLANK(AI7:AT7))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test of
an IF() formula.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the same as
it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made
an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come
up
with
any thing.

Count if not a number
 

Try this:

=COUNT(AI7:AT7)=12
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the same as it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made
an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come up
with
any thing.

Count if not a number
 

Or the more generic

=COUNT(AI7:AT7)=COLUMNS(AI7:AT7)


--


Regards,


Peo Sjoblom


"RagDyeR" wrote in message
...
Try this:

=COUNT(AI7:AT7)=12
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the same as
it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made
an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come
up
with
any thing.

Count if not a number
 

Still dosent work with the blanks, I tried this formula, but with the same
result.

=IF(COUNT(K1:K20)+(COUNTBLANK(K1:K20))=COUNTA(K1:K 20)+(COUNTBLANK(K1:K20)),"NO","YES")

"Sandy Mann" wrote:

=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test of
an IF() formula.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the same as
it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made
an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come
up
with
any thing.

Count if not a number
 

I beleive I have found it

=IF(COUNT(K1:K20)=COUNTA(K1:K20)+(COUNTBLANK(K1:K2 0)),"NO","YES")

is there any flaw in this formula, I have tried it with all of the invalid
characters I could thnk of, so far it works

Thanks for all of your help!


"D" wrote:

Still dosent work with the blanks, I tried this formula, but with the same
result.

=IF(COUNT(K1:K20)+(COUNTBLANK(K1:K20))=COUNTA(K1:K 20)+(COUNTBLANK(K1:K20)),"NO","YES")

"Sandy Mann" wrote:

=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test of
an IF() formula.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the same as
it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users made
an
error by entering a blank, a space, a period ect. Where numbers should
have
been enterd. I have tried several count variations and could not come
up
with
any thing.

Count if not a number
 

"Peo Sjoblom" wrote...
Or the more generic

=COUNT(AI7:AT7)=COLUMNS(AI7:AT7)
....

More generic still,

=COUNT(rng)=COLUMNS(rng)*ROWS(rng)

=COUNTIF(rng,"=")+COUNTIF(rng,"*")=0

=AND(ISNUMBER(rng)) [array formula]

Count if not a number
 

Don't RagDyeR, Peo & Harlan's formulas work for you?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I beleive I have found it

=IF(COUNT(K1:K20)=COUNTA(K1:K20)+(COUNTBLANK(K1:K2 0)),"NO","YES")

is there any flaw in this formula, I have tried it with all of the invalid
characters I could thnk of, so far it works

Thanks for all of your help!


"D" wrote:

Still dosent work with the blanks, I tried this formula, but with the
same
result.

=IF(COUNT(K1:K20)+(COUNTBLANK(K1:K20))=COUNTA(K1:K 20)+(COUNTBLANK(K1:K20)),"NO","YES")

"Sandy Mann" wrote:

=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test
of
an IF() formula.
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
That is almost what I want, but I need it to treat the blanks the
same as
it
treats a space or a coma

"Sandy Mann" wrote:

Does;

=COUNT(AI7:AT7)=COUNTA(AI7:AT7)

which will return TRUE or FALSE doe what you want?
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"D" wrote in message
...
I have a range of cells AI7:AT7 and I need a count to see if users
made
an
error by entering a blank, a space, a period ect. Where numbers
should
have
been enterd. I have tried several count variations and could not
come
up
with
any thing.

Count if not a number
 

It seems to be 4 characters longer than your previous formula, Sandy? I
wondered whether you meant shorter in execution time, but I would have
doubted it.
--
David Biddulph

"Sandy Mann" wrote in message
...
Slightly shorter:

=SUM(COUNT(AI7:AT7),--ISBLANK(AI7:AT7))=SUM(COUNTA(AI7:AT7),--ISBLANK(AI7:AT7))

"Sandy Mann" wrote in message
...
=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test
of an IF() formula.
....

Count if not a number
 

So it is, I checked the lengths with LEN() but I must have confused the
returned numbers. In any case it is academic because it does not work.
RagDyeR, Peo & Harlan posted working formulas so I did not bother trying to
improve it.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
It seems to be 4 characters longer than your previous formula, Sandy? I
wondered whether you meant shorter in execution time, but I would have
doubted it.
--
David Biddulph

"Sandy Mann" wrote in message
...
Slightly shorter:

=SUM(COUNT(AI7:AT7),--ISBLANK(AI7:AT7))=SUM(COUNTA(AI7:AT7),--ISBLANK(AI7:AT7))

"Sandy Mann" wrote in message
...
=COUNT(AI7:AT7)+SUM(ISBLANK(AI7:AT7))=COUNTA(AI7:A T7)+SUM(ISBLANK(AI7:AT7))

Agan TRUE or FALSE. If you want something else then use it as the test
of an IF() formula.
...