Forum Discussion
Counting instances for the previous week only
=SUMPRODUCT(($A$2:$A$23=E2)*($B$2:$B$23>=TODAY()-WEEKDAY(TODAY(),2)-6)*($B$2:$B$23<=TODAY()-WEEKDAY(TODAY(),2)))This formula counts the instances for the previous week from 07/11/22 to 13/11/22. During the current week the result for 07/11/22 to 13/11/22 doesn't change.
By next monday (21/11/22) and during the whole next week it counts the instances from 14/11/22 to 20/11/22 and so on. Actually you don't need a 'week commencing' box to update each monday.
=SUMPRODUCT(($A$2:$A$23=E2)*($B$2:$B$23>=TODAY()-WEEKDAY(TODAY())-5)*($B$2:$B$23<=TODAY()-WEEKDAY(TODAY())+1)*$C$2:$C$23)You can try this formula.
QP
That's similar to what I require, thanks.
But all I'd need it to do is total how many times Rig A & Rig B are listed for the last week. Each output is the mention in the rig in the list, if that makes sense.
It also needs to be fixed to the Monday of the week after, so it shows the previous week's data, not the last 7 days as of today...
So... in your example the output I need is:
Rig A = 6
Rig B = 8
which covers week dates 07/11/22 to 13/11/22 (mon to sun previous week)
I have a 'week commencing' box which I update for each monday, so it should then work on the previous seven days only...
Does that make sense?
Cheers
Nick
=SUMPRODUCT(($A$2:$A$23=E2)*($B$2:$B$23>=TODAY()-WEEKDAY(TODAY(),2)-6)*($B$2:$B$23<=TODAY()-WEEKDAY(TODAY(),2)))This formula counts the instances for the previous week from 07/11/22 to 13/11/22. During the current week the result for 07/11/22 to 13/11/22 doesn't change.
By next monday (21/11/22) and during the whole next week it counts the instances from 14/11/22 to 20/11/22 and so on. Actually you don't need a 'week commencing' box to update each monday.
- That's the One!
Fantastic! Thank you. Very much appreciated.
Cheers
N