| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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] |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. .... |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. ... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count rows and insert number to count them. | Excel Discussion (Misc queries) | |||
| count each cell that have a number and take that number and count. | Excel Discussion (Misc queries) | |||
| Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
| count the number of times the same number shown | Excel Discussion (Misc queries) | |||
| Count number of times a specific number is displayed in a cell ran | Excel Worksheet Functions | |||
Linear Mode
