Formula to summarize multiple values based on date range value

[Nagendra PrasadBS]

Hello,
I wanted to get the summarized value for each branch 3months pre and 3months post based on branch date_moved for each branch as per the below values in excel.

I have tried using Sumifs with multiple value criteria but it's not working. Please help me out to crack the criteria.

Thanks in advance.

Prasad

[#2]

Hi Nagendra,

You may use combination of OFFSET and MATCH. For this model (as in your picture)

for given date_moved 3 months before is found as

=OFFSET($D$1,MATCH($C23,$D$2:$D$19,0)-3,0)

where MATCH finds the position of the date and OFFSET gives you the value in 3 rows back. Similar 3rd month forward.

To SUM three months in addition you find the position of the Reg column in your range, define in OFFSET the height of your range in 3 cells back/forward from current date and given Reg cell, and sum the range. Finally

=SUM(OFFSET($D$1,MATCH($C23,$D$2:$D$19,0)-3,MATCH($D23,$D$1:$G$1,0)-1,3))

That was back, forward is simular. All set is in attached file.