1. ## count formula (2003)

Hi,
i have data in Column a1:a20, each cell data consist of a number and text, the text is not uniform, and i want to get their counts in a21.

Regards,

2. ## Re: count formula (2003)

I am not exactly sure what you are after, but I think some of the techniques Bob Umlas does in Array Formulas should help.

He has an array formula for:
Extracting Just The Numeric Portion Of A Cell
Calculating The Sum Of The Digits

If you need additional help can you elaborate on what you want to do?
Steve

3. ## Re: count formula (2003)

Thanks Steve,

the attached file has sample of what i want.. under the heading Soft A2:A10 i have such data, and i won get the count of the non-blank cells in a11.

dubdub

4. ## Re: count formula (2003)

Try
=counta(A2:A10) this counts all none blank cells.

5. ## Re: count formula (2003)

Thanks Jerry.

dubdub

6. ## Re: count formula (2003)

jerry,

one more thing, any explanation why it does not update it self when i delete one of the values, the result still 7rather than 6.
dubdub

7. ## Re: count formula (2003)

Should do

Go to Tools|Options|Calculation

and see if the Calculation option is set toautomatic

still 7.

9. ## Re: count formula (2003)

It works for? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Have a look at mine and compare

10. ## Re: count formula (2003)

Well it works perfectly well from my laptop, adding and deleting. If I change the options like I showed you in <post:=637,659>post 637,659</post:> it doesn't. This is a standard formula and nothing special about it.

There is not much more I can say.

11. ## Re: count formula (2003)

ahaha

Have a look at a response from Hans <post#=632,031>post 632,031</post#> it looks like you have your calculations set to manual again Try pressing F9 and see what happens to your calculation now. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

12. ## Re: count formula (2003)

jerry,

the puzzle is when i delete, when i add a new value it adjust, and guess what, when i delete the cell value using the space bar no change,still 7,and it take effect only when i use the delete button.

dubdub

13. ## Re: count formula (2003)

You have edited your message since I posted so the thread is in danger of losing sense.

When you delete text or formulas using the space bar you are in fact just replacing the value with a space character which is an ASCII character. As it is now a value (like a letter or a number) the counta function will count it as it is no longer blank. The best thing to do is not delete using the space bar.

14. ## Re: count formula (2003)

sorry for that, and thank you for the help.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•