# Calculating top 5 weekly stats automatically?

Using a formula that another EE'er brilliantly and generously offered me, I am able to display cumulative top five stats, but what if I also want to display the current week's top five?

This formula gives me the cumulative top five:
=QUERY(A20:D170, "SELECT A, D ORDER BY D DESC LIMIT 5")

With a small change, this formula gives me the current week top 5:
=QUERY(A20:J170, "SELECT A, (I+J) ORDER BY (I+J) desc LIMIT 5")

But I need to manually change (I+J) to (K+L ) for the next week, and so on. Is it possible to automate this using the (7 days <= date) in row 11?

Here is the test sheet (feel free to edit it):

See the yellow highlighted section. :)

Thank you so much for your help.

Last Comment
Saqib Husain
Saqib Husain

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Doug Van

Greetings Saqib,

Thank you so much for your solution. At first, I thought it wouldn't work because I envisioned it working based on reading the dates in row 11 and comparing to the current date. In the completed sheet, the Weeks will be displayed across the page in row 10, but you couldn't have known that. :)

Anyhow, I can make your solution work by placing a conditional statement in row 10 to only display the "Week x" heading when data is entered. Example: =If(K14>0,"Week 6","")

Out of curiosity, I tried to incorporate "=INT(((Today())-1)/7)*7+7" (find the next Saturday), but had no luck reworking your solution.

Thank you.
Saqib Husain

Do you need any more assistance or have you achieved whatever you wanted?
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY