How to increment add +1 and keep in memory

You could use a UDF with a static variable, although that will be cleared when you close the workbook.

In a standard module:

Please Login or Register  to view this content.

Then in B3, put the formula

Formula:

Please Login or Register  to view this content.

Wow, that's exactly it!
I can even make copies of it in the same module for another value, 99 instead of 100 and so on.
Thank you very much!

But be careful! CountNum100 function should only be used for one cell.
Suppose you have three cells A1, B1, and C1 that contain the function =RANDBETWEEN(1,100). In cells A3, B3, and C3, you will use =CountNum100(A1), =CountNum100(B1), =CountNum100(C1), respectively. The results in cells A3:C3 will return the sum of all occurrences of 100 in cells A1:C1, not each one individually. Besides, at any given moment, each of the cells of the range A3:C3 may show a false value of the sum.

Artik

If you wanted it for multiple cells you could maybe use a dictionary

Please Login or Register  to view this content.