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

#### Squiget

##### New Member
Hi all,

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Eric W

##### MrExcel MVP
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

##### MrExcel MVP, Moderator
How about in B3 copied right
=SUM(\$A2:A2)

#### Squiget

##### New Member
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))

1,089,437
Messages
5,408,214
Members
403,190
Latest member
RBrite