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

[SVTF]

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 ?

[shg]

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))

[SVTF]

with the above ...

D3 changes to 1 with something in A5
but when something added to A6 the count in D3 stays at 1

Tried changing the date and entered again in A6 and D3 stayed at 1

[shg]

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

[SVTF]

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.

[shg]

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.

[SVTF]

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

[shg]

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

[SVTF]

Works like a charm - Thanks.

Another step if possible.

Is there a way to capture the value of D3 prior to it going to 1 when the date flips over?

And then put that value on a different sheet in the work book?

[SVTF]

=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.

[SVTF]

I need D3 to add up each entry inputed in column A through out the day ...

Then when I come in the next morning and open the workbook D3 (on each worksheet) would reset to 0 or blank (clear) because it is now a different day.

[shg]

Then you could just use

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

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

[SVTF]

Then you could just use

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

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

Thanks, all you guys must hate us noobies.

Looks like it will work.