CODEWITHSUNDEEP
excelsumvlookup
CBooth
CBooth

Reputation: 41

Lookup date, return sum of previous 5 days

I am needing a bit of help on a formula which I have been amending for a while now with no success going through vlookups, indexmatches, sumproducts etc.

Basically, on one tab "weekly summary" I have the date contained in cell H2 which will only ever be set to a monday. I need a formula in cell B3 that will look up to a different tab "Raw Data Input" based on this date in cell H2, and return the sum of the values for the previous week (previous 5 amounts using the workdays formula on raw data input tab).

So for instance if this was today, 14th Nov, I would want the sum of the values from 7th-11th Nov.

Is this possible without doing 5 separate vlookups to different dates?

Ps. I will eventually form this into a macro, however as I have a number of tasks to complete before I get to that stage, I would prefer it as a formula, to get everything set up before I start adding VBA.

Upvotes: 1

Views: 281

Answers (1)

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

You can use SUMIFS where your multiple conditions are dates are less than 14th Nov, but greater than 14th Nov - 7

=SUMIFS(A:A,B:B, "<"&$H$2,B:B, ">"&$H$2-7)

Whereby A:A is the sum range, and B:B is the columns with dates.

Upvotes: 3

Related Questions