Count last 7 days of pupil attendance that exceeds 31

Hi all


was after some help with an excel formula!!! does anyone know what formula I would use to adjust the starting point of its criteria for example if I had a spreadsheet of pupil attendance by day but then wanted a formula to show how many days I had 31 pupils in but only counting for the last 7 days(values)? so then each time I add a new value it would still only count back 7 days.
if the starting point was the same I would use COUNTIF(Sheet1!B:C,31) but how do I add only count the last 7 values without having to adjust the formula each time I add a value. as below

day 1 - 30
day 2 - 34
day 3 - 31
day 4 - 32
day 5 - 31
day 6 - 31
day 7 - 33

so when I add day 8 it would only count days 2,3,4,5,6,7 and 8. then when I add day 9 it will count days 3,4,5,6,7,8 and 9.

thank you in advance for any help

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(I'll change the title for you on this occasion since this is your first post, but please remember for the future.

Try this:

=COUNTIF(OFFSET($B$1,LOOKUP(2,1/(ISNUMBER($B$1:$B$200)),ROW($A1:$A200))-7,0,7),">="&31)

=COUNTIF(INDEX(B:B,LOOKUP(9^9,B:B,ROW(B:B))-6):INDEX(B:B,LOOKUP(9^9,B:B,ROW(B:B))),">="&31)
Try the above if not worked attach sample excel file

Please Login or Register  to view this content.

@ Richard Buttrey, I am sorry, I did't see your post regarding change title,
wilson23 pls edit your thread title as suggested by Rechard buttrey