I have a worksheet with dates in row 1 and data values in rows 2 - 5. The data is then sub-totalled in row 6. The dates start from G1 and the date increases in the next column to the right. Currently this goes up to column EZ.

I want to get the sum of data from Row 6 for the last 7 days.

Today's date is in EZ1 so I have the following to get the first cell. =OFFSET(EZ1,,-7) I can sum the last week using: =SUM(EZ6:OFFSET(EZ6,,-7))

But how can I get this to update automatically so that I the value is always the most recent?

  • 2
    What have you tried?  Can you write formulas to express the beginning and end of last week, month, etc., or are you asking for help with that, too? – Scott May 22 '15 at 5:55
  • More details are needed. This site isn't for teaching how to use Excel, rather to help you when you run into a problem or get stuck. Please tell us what you have researched and attempted to resolve this. Take a minute to read How to Ask to improve your question. – CharlieRB May 22 '15 at 12:44
  • Please expand you question to show sample data (layout, format, etc.), and be more explicit about any constraints, desired output, and exactly what you want to do. Keep in mind that Super User isn't a free coding service, but people will help you solve a specific problem. You are more likely to get help (and better help), if people don't have to guess at exactly what your trying to do, or spend time on a wild goose chase developing answers that aren't what you're looking for. – fixer1234 May 22 '15 at 15:43

I would use the Power Query Excel Add-In for this. It has an Unpivot command which can convert your variable column list into a row for each cell. Here's a detailed example of Unpivot:


I would first use a Filter to just keep the data for row 6. "Keep bottom rows" might work - depends on your layout. Then I would use the "Unpivot other columns" command to transform the columns into rows (dynamically, allowing for future column changes).

Next step would be to filter for just the last 7 days. Date columns allow for "Date Filters", e.g "In the previous X days".

Final step would be a Group By - remove the Group By column and just Sum the Value.

Its a few steps to get there, but no coding is required, so its actually simpler than writing formulas. At each step you can see the result of the previous commands.

  • Thanks for your suggestion but I'm sure there should be a way to do this without an add-in which would then work on any standard version of excel which is what I'm looking for. – TomC Jun 11 '15 at 12:04
  • Well several weeks have passed and no other suggestions have been offered - presumably you have drawn a blank also. So I'm not sure what the basis for your confidence is. Note that Power Query is produced by Microsoft, and will be included in Excel from 2016 onwards: support.office.com/en-gb/article/… – Mike Honey Jun 12 '15 at 1:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.