Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Find the SUM of the Latest Values in a Table

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.


Background:
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).

Request:
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:
=SUM(INDIRECT("B"&MAX($K$2:$K$366)&":B"&MATCH(10^10,B:B,1)))
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]):
=SUMPRODUCT((COUNT(OFFSET($B$1,ROW(2:366)-1,0,$D$8*2,1))=$D$8)*ROW(2:366))
but Excel doesn't like the ROW(2:366) argument inside the OFFSET and it fails.

Thanks,
-Glenn
SumLatestValues.xlsx
0
Glenn Ray
Asked:
Glenn Ray
1 Solution
 
krishnakrkcCommented:
Try this array formula

=SUM(INDEX(Table1[Sales],MATCH(9.999E+307,Table1[Sales])):INDEX(Table1[Sales],LARGE(IF(Table1[Sales]<>"",ROW(Table1[Sales])),D8)-1))

Kris
0
 
Glenn RayExcel VBA DeveloperAuthor Commented:
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.

Regards,
-Glenn
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now