SOLVED

Counting instances for the previous week only

Copper Contributor

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

5 Replies

@Nick_W1890 

=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.

rig.JPG 

@Nick_W1890 

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?

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

best response confirmed by Hans Vogelaar (MVP)
Solution

@Nick_W1890 

=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.

rig A and B.JPG

 

That's the One!

Fantastic! Thank you. Very much appreciated.

Cheers

N
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@Nick_W1890 

=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.

rig A and B.JPG

 

View solution in original post