Find the SUM of the Latest Values in a Table

Posted on 2014-09-18
Medium Priority
Last Modified: 2014-09-18
This is a re-packaging of a series of questions recently posed regarding calculations needed on the bottom-most or most-recent data in a table.

The attached workbook contains an Excel Table showing daily sales data.  Sales are only recorded on weekdays and non-holidays, so there are gaps in the table and that is normal (if not ideal).

There is a need to show the last N-number of days of sales, counting by the sales reported.  Currently, there are a set of formulas (H8 and I8 to determine the rows containing the latest sales within N-days), a helper column (K) of functions used to determine the first applicable row, and a final formula to sum up the total (F8).

Create a single formula in F8 that replicates this process without the need of the functions in H8 and I8 or the helper column in K.

Note that the formula in F8 can immediately be re-written as:
but I've left it "dependent" so you can see the steps better.

The key problem for me has been trying to create an array function that reproduces the result in H8.  I've tried this as an array function ([Ctrl]+[Shift]+[Enter]):
but Excel doesn't like the ROW(2:366) argument inside the OFFSET and it fails.

Question by:Glenn Ray
LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 40331996
Try this array formula


LVL 27

Author Closing Comment

by:Glenn Ray
ID: 40332013
I completely forgot about the LARGE function!

My own preference is to swap the two INDEX functions for the first and last cells, but otherwise, this is an excellent solution.


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question