Code to count and then reset to 0 or blank out.

Hey,

Not sure if this is possible.

I have a simple count function in D3 - every time there is an entry in A5:A150 it counts up by 1.

Please Login or Register  to view this content.

In A5 thru A150 will be a date, is there a way to have D3 reset to 0 and start counting again once the date is not the same as the cell above?

Is there then a way to capture the number in D3 prior to it reseting to 0 ?

Posting an example (workbook) would help. If the dates are entered in ascending order, changing occasionally, then maybe this is what you want:

=COUNTIF(A5:A150, MAX(A5:A150))

So post an example workbook and explain what you want in context.

Originally Posted by shg

Posting an example (workbook) would help

Here is a sample ... D3 = 5 at the moment.

So its now April 12th and A10 is about to show 12-Apr ...

Would like D3 to reset to 0 as soon as 12 -Apr is added to A10

And if possible, Capture the value of D3 prior to reseting to 0 - and placing that number on another worksheet.

Why should D3 reset to 0 instead of 1; what's it counting?

Is it supposed to reset because the date rolled over?

If you change the formula in D3 to =SUMPRODUCT(--(INT(A5:A150) = INT(MAX(A5:A150)))), it will change to 1 each time the date rolls over, and then count forward from there.

Cool that works for the date/counting part ...

When nothing is in A5 to start D3 = 146 ? its counting the blank cells total.

Would like D3 to be blank or 0 until a date is populated in A5 then D3 changes to 1

=if(count(a1:a150), sumproduct(--(int(a5:a150) = int(max(a5:a150)))), "")

Originally Posted by shg

=if(count(a1:a150), sumproduct(--(int(a5:a150) = int(max(a5:a150)))), "")

Thanks again to shg

This worked but once applying in, need it tweaked a little.

Then you could just use

=sumproduct(--(int(a5:a150) = today()))

... to tell you how many entries have today's date.