Progressive Count

It is possible to have excel to make a progressive count even if i clear the field of cells .
For example: in cell A11 i have =COUNT(A1:A10)... for a value of (10) in A11 ... then i clear content in A1:A10 and type new values and keep a progressive total in A11.

I have attached a workbook for reference

Any help is greatly appreciated!!!

Originally Posted by Score

Does anyone have a word for this formula?

"Impossible" comes to mind. Excel Functions will not remember what came before. You could use VBA to do this if you want to go that route. Macros will always need to be enabled.

It is possible with VBA. Are you okay with a VBA solution?

Right click on the spreadsheet tab key and paste this code into the worksheet module

Please Login or Register  to view this content.

Let me know if that does what you want it to do. Disadvantages:
1. Macros must be enabled
2. If the person makes an error on entering, it will still add it to A11. You'll need to manually subtract it.

Here's my version:

Please Login or Register  to view this content.

It assumes that, per the thread title, a progressive count (not sum) is required. Errorhandling ensures that Application.EnableEvents is always set back to True.

Hi,

You might want to update your profile which shows your Excel version as 2003. I know very little about Excel 2004, but I do believe that it supports VBA.

I've attached your original workbook with the code embedded. If you change a cell within A1:A10 then the counter in A11 will increase by 1 (provided that the cell wasn't cleared). Macros must be enabled for it to work.

hi Colin,

I'm a nosy chap & have a couple of questions about your code...
- Why are you defining the count variable as a Double (instead of Long)?
- ditto for using the "#" sign on the end of the 1 & the end of the 0?

Rob

Hi Rob,

Some good questions there.

Why are you defining the count variable as a Double (instead of Long)?

When I was writing the code I knew that the count variable would end up being put on (or added to) a worksheet. All numbers on worksheets are Double data types, so I declared it as one. The code assumes that A11 contains a number (or a value which can be coerced to a number) otherwise an error will be raised.

ditto for using the "#" sign on the end of the 1 & the end of the 0?

As you know, # is the type declaration character for a Double data type. In the VBA IDE, if you type 1 in your code it will be read as an Integer. If you type 1# in it will be read as a Double. Since I am either adding or comparing Doubles, I used #.

Normally I wouldn't use type declaration characters on forum posts because I think they make the code more confusing to people.

Hi,

Sure, see attached workbook.

Hi Colin Legg

Than you very much. It was doing exactly what i need it to do but for some reason, now that i have it integrated to the rest of the data does not want to count. It suppose to count every time i type the points on rows G16:K16 - G17:K17 and so on for each color. The only change i did is to check mark the iteration for the circular reference formula on F46:F55 and J46:J55. I have attached a workbook for reference with all the elements in place. Any advice?

Regards.


Regards

Thanks Colin (re post # 12),

I never knew that about cells containing numerical values being identified as Doubles, but I was able to support it pretty quickly via the ImmediatePane with the below statements

Please Login or Register  to view this content.

Score,
I haven't looked at your circular references, but to make Colin's event code work:
right click on the sheet tab & View Code, then change the range stated in the below line of code ("X9:X48") to whatever range you want the action to relate to.

Please Login or Register  to view this content.

hth
Rob

hi Score,

Sorry about the slow response.

If you still need help with this question, can you please post the latest version of your file with the code you are currently using?
Colin may understand from your last post, but I need more clarification, so can you please also include "before & after examples" in the file showing what you expect to see, with a brief explanation of the logic?
If you no longer need help, can you please post your solution & mark the Thread as Solved?

Thanks
Rob

hi Score,

I'm off to sleep now, but I have asked if someone else can help out before I get back to your thread...

Fingers crossed eh?
Rob