Trying to use SUMIF or SUMIFS to return a value if it was entered after a certain time.

[Owenn1986]

Hi,

So the problem i'm trying to solve is basically to see the value (in 1 formula) and volume (in another) of card payments our business takes after 18:00.

At the moment i have a timestamp on column S which stamps when someone puts a payment on the sheet. and a value goes in column F

I want to find both the sum of and the count of payments taken after 18:00 but i cant for the life of me get it to work.

I've Tried : =SUMIF($S$20:$S$308,"<TIME(17,59,59)",$F$20:$F$308) & a similar SUMIFS but the always return a value of 0.00

any help would be appreciated

And im basically a novice so keep it me proof LOL

Thanks in Advance for any help

 

[Bo_Ry]

Try
=SUMIF($S$20:$S$308,">="&TIME(18,0,0),$F$20:$F$308)
or
=SUMIF($S$20:$S$308,">=18:00",$F$20:$F$308)

 

[Owenn1986]

Hi

Thanks

That returns a value now, just unfortunately the wrong one, it's been twisting my noodle for about an hour and a half now

 

[Owenn1986]

Hi

I'm still struggling to get this to work, i've tried all 3 variations listed and i can get it to return a value but its completley wrong.

Could it be something to do with the stamp? or maybe the formatting of the cells?

(This is what i put for the time stamp)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
If Target.Cells.Count > 3 Then Exit Sub
With Target.Offset(0, 16)
.Value = Now
.NumberFormat = "hh:mm"
End With
End Sub


I could really use some help please