Formula to summarize multiple values based on two cell values and column values

%3CLINGO-SUB%20id%3D%22lingo-sub-112994%22%20slang%3D%22en-US%22%3EFormula%20to%20summarize%20multiple%20values%20based%20on%20two%20cell%20values%20and%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-112994%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3EI%20wanted%20to%20get%20the%20summarized%20value%20for%20each%20branch%203months%20pre%20and%203months%20post%20based%20on%20branch%20date_moved%20for%20each%20branch%20as%20per%20the%20below%20values%20in%20excel.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20using%20Sumifs%20with%20multiple%20value%20criteria%20but%20it's%20not%20working.%20Please%20help%20me%20out%20to%20crack%20the%20criteria.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance.%3CBR%20%2F%3E%3CBR%20%2F%3EPrasad%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-112994%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETips%20and%20Tricks%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-113786%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20summarize%20multiple%20values%20based%20on%20two%20cell%20values%20and%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-113786%22%20slang%3D%22en-US%22%3EHave%20a%20play%20with%3CBR%20%2F%3E%3DINDEX(G%244%3AG%2414%2CMATCH(G%242%2CF%244%3AF%2414%2C1)-1)%2BINDEX(G%244%3AG%2414%2CMATCH(G%242%2CF%244%3AF%2414%2C1)-2)%2BINDEX(G%244%3AG%2414%2CMATCH(G%242%2CF%244%3AF%2414%2C1)-3)%3CBR%20%2F%3Ethe%20f%244%3Bf%2415%20is%20where%20the%20year%2FMonth%20and%20F%244%20is%20where%20the%20move%20date%20year%2Fmonth%3CBR%20%2F%3EI%20would%20go%20with%20working%20out%20the%20match%20part%20first%20also%20try%20year*100%2B%20month%20as%20a%20number%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor
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
1 Reply
Have a play with
=INDEX(G$4:G$14,MATCH(G$2,F$4:F$14,1)-1)+INDEX(G$4:G$14,MATCH(G$2,F$4:F$14,1)-2)+INDEX(G$4:G$14,MATCH(G$2,F$4:F$14,1)-3)
the f$4;f$15 is where the year/Month and F$4 is where the move date year/month
I would go with working out the match part first also try year*100+ month as a number