sumifs and offset

[zhoward6702]

Help!!

In a separate sheet I am trying to reference another sheet and sum the previous 7 cells based on two variables. On sheet 1 I have a list of property's in column A starting on line 2. B1:R1 are dates (my second variable). On sheet 2 I want to sum the values in sheet 1 b2:h2 (so 7 cells) and match the property and date.

so I want excel to find the property name and then find the value for the date 7/7/17 and then sum the previous 7 values for each property. So find value in column for 7/7/17 and sum back seven cells.

 

[Marcelo Branco]

Assuming the dates in Shet1 B1:R1 are in ascending order (1/7/17, 2/7/17,...) maybe something like this in Sheet2

=SUMIF(Sheet1!$B$1:$R$1,"<="&B2,INDEX(Sheet1!B:R,MATCH(A2,Sheet1!A:A,0),0))
where
B2 contains the date (7/7/17)
A2 contains the property name

Hope this helps

M.

 

[zhoward6702]

This worked for the first 7. But I need to drag this formula to the right and then sum the next 7. so from 7/8 - 7/14...

 

[Marcelo Branco]

This worked for the first 7. But I need to drag this formula to the right and then sum the next 7. so from 7/8 - 7/14...

Please, try to show us the whole scenario. I didn't know you need to copy the formula across.

*Maybe* (guessing) something like this in Sheet2

	A​	B​	C​	D​	E​	F​	G​
1​	Property​	07/07/2017​	07/08/2017​	07/09/2017​	07/10/2017​	...​	...​
2​	Property1​
3​	Property3​

Formula in B2 copied across
=SUMIF(Sheet1!B$1:R$1,"<="&B$1,INDEX(Sheet1!B:R,MATCH($A2,Sheet1!$A:$A,0),0))

M.