Possible to count how many value enter in the most recent input for a column in excel?

[jiaweilee]

Is there a way to count how many data is enter into a column for the most recent input?

 

[oldbrewer]

if a1 a2 a3 entered yesterday and a4 a5 today - do you want to count a4 and a5 = 2

can you put the date of entry against each input, then it will be really simple.

 

[jiaweilee]

When I enter an input in A1, I have a macro that run to auto input the date in B1

 

[oldbrewer]

[TABLE="width: 718"]
<colgroup><col><col><col span="6"><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]27/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]27/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]27/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]28/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]28/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]28/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]28/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]28/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]today's date[/TD]
[TD="align: right"]28/03/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]how many entered today ?[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]formula giving 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]=SUMPRODUCT(($B$1:$B$8=I8)*1)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jiaweilee]

[TABLE="width: 320"]
<tbody>[TR]
[TD]For each data I have the one that enter in the same day but different timing.


[TABLE="width: 320"]
<tbody>[TR]
[TD]1382242564[/TD]
[TD][/TD]
[TD]29-03-16 7:07[/TD]
[/TR]
[TR]
[TD]1382242586[/TD]
[TD][/TD]
[TD]29-03-16 7:07[/TD]
[/TR]
[TR]
[TD]1382242565[/TD]
[TD][/TD]
[TD]29-03-16 7:07[/TD]
[/TR]
[TR]
[TD]1382242285[/TD]
[TD][/TD]
[TD]29-03-16 7:07[/TD]
[/TR]
[TR]
[TD]1382242358[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242451[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242460[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242417[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242332[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242459[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242455[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242458[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242448[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
[TR]
[TD]1382242447[/TD]
[TD][/TD]
[TD]29-03-16 11:19[/TD]
[/TR]
</tbody><colgroup><col span="2"><col></colgroup>[/TABLE]
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

same principle - in my answer where I put today's date put 29-03-16 11:19

 

[thisoldman]

If the date+time always increase down the column, we can modify oldbrewer's formula: =SUMPRODUCT(--(B1:B14=MAX(B1:B14)))

But my first thought was to use: =COUNTIF(B1:B14,MAX(B1:B14))

 

[oldbrewer]

and I was thinking make a helper cell (say AZ1) =max (B1:B10000) and then use my sumproduct formula, but countif does the job as well, as would a pivot table I think...

 

[jiaweilee]

I tried to do the it without the helper cell with this but the result always return 0.

 =SUMPRODUCT(($A$2:$A$20<>"")*(AND($C$2:$C$20>($E$2-($F$2/86400)),$C$2:$C$20<($F$2/86400+$E$2))))

 

[jiaweilee]

I tried to do the it without the helper cell with this but the result always return 0.

 =SUMPRODUCT(($A$2:$A$20<>"")*(AND($C$2:$C$20>($E$2-($F$2/86400)),$C$2:$C$20<($F$2/86400+$E$2))))

solved

=SUMPRODUCT(($A$2:$A$20<>"")*($C$2:$C$20>($E$2-($F$2/86400))*($C$2:$C$20<($F$2/86400+$E$2)))