Forum Discussion
Counting instances for the previous week only
Hi, All.
I am compiling a spreadsheet that shows the output of two piling rigs. I have the total to date of each one using the 'COUNTIF' formula on the Rig Number column, but what I would like to do is also show the output of each rig for the previous week based on a week commencing Monday.
I have a rig number column and a date piled column. How can I make it work to combine the two for a box to show 'Last Week Output R1' & 'Last week output R2' ?
Help gratefully appreciated.
TIA
N
=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.
5 Replies
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
=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.
