Index/Match Formula - Sum if falls between a date range

[Squiget]

Hi all,

Please can someone help?

I am trying to find a tidier version of the below formula - what I am trying to do is basically add up the values in row 2 for the week before. The displayed dates are Mon-Fri (not inc Sat-Sun).

Note, the below formula would be in cell F3, so on the 2nd Dec, I am looking up the values for 25th Nov-29th Nov, and adding these up.
=SUM(INDEX(2:2,MATCH(F1-7,1:1,0)),INDEX(2:2,MATCH(F1-6,1:1,0)),INDEX(2:2,MATCH(F1-5,1:1,0)),INDEX(2:2,MATCH(F1-4,1:1,0)),INDEX(2:2,MATCH(F1-3,1:1,0)))

	A	B	C	D	E	F	G	H	I	J
1​	25-Nov​	26-Nov​	27-Nov​	28-Nov​	29-Nov​	02-Dec​	03-Dec​	04-Dec​	05-Dec​	06-Dec​
2​	100​	200​	300​	400​	500​	600​	700​	800​	900​	1000​

Thanks

 

[Eric W]

Why not just:

=SUM(A2:E2)

or if you want to only show the total on Mondays:

=IF(WEEKDAY(F1)=2,SUM(A2:E2),"")

 

[Fluff]

How about in B3 copied right
=SUM($A2:A2)

 

[Squiget]

Hi guys,

Thanks for your responses. I think I over-simplified what I was trying to do.
I said that I wanted to sum up for the previous week, but really I need my formula to look up the 27 weeks previous - I was hoping to get a simple formula which I could then just adjust for the number of days/weeks.

I am trying to look up what was drawn down on a loan 189 days ago, to predict what I will need to pay back in the set week (27 weeks from drawn down date).

I have been having a play and I think the sumifs function will serve my purpose
Cell F3 = SUMIFS(2:2,1:1,">="&(F1-7),1:1,"<"&(F1-2))
(But in reality I will use SUMIFS(2:2,1:1,">="&(F1-189),1:1,"<"&(F1-184))

Thanks for your help