Forum Discussion
Solved
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 t...
=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.
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?