Retaining value from a formula if new result becomes in error

[See attached file as an example]

I want to be able to manage an auto count of items in a spreadsheet based on the current date. This I can do:

[A1]
=today()

[A3]
22/09/2010

[B3]
=if(A1=A3,countif(range,criteria)


Problem
I would also like to have a history of counts. My issue is having making this an automated exercise.

[A4]
22/09/2010
[A5]
23/09/2010
[A6]
24/09/2010


[B3]
=if(A1=A4,countif(range,criteria)
[B4]
=if(A1=A5,countif(range,criteria)
[B5]
=if(A1=A6,countif(range,criteria)

Issue
When a new date occurs, I loose the previous value. Is there some automated way to retain this value before it goes into error?

Hi Paul,
Try put this code in cell G8

=IF(F8=$G$5,COUNTIF($C$8:$C$11,$G$7),"")
copy down

then put this code in cell K8

=IF(G21<>"",G21,IF(J21=$K$5,COUNTIF($C$8:$C$11,$K$7)))
copy down

Well I got it.
Before you type this formula, go to tools | option | Calculation (for Excel 2003)
Check the Iteration, then change the Max Iteration value to 1
Then in Cell G8 type this formula :
=IF(G8<>"",IF(F8=$G$5,COUNTIF($C$8:$C$11,$G$7),G8))

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Originally Posted by paulus_c

What are the implications of selecting "Check the Iteration, then change the Max Iteration value to 1" to other aspects of a workbook?

There will no implication with the other workbook. The option only apply to current Workbook.