SOLVED

Sum values across ROWS in LookUp

New Contributor

I have what should be an easy question...and yet...

 

Let's say I have a data table as follows:

 

Jan56
Feb92
Mar48
Apr87
May66
Jun99
Jul78
Aug83
Sep69
Oct72
Nov81
Dec59

 

My input is month, and I want to SUM values for the matching month and 2 previous months. So if the input value is "Sep", I want to return 69+83+78 = 230.

 

How can I do this?

3 Replies
best response confirmed by PRisman (New Contributor)
Solution

Hi @PRisman,

You can do this with MATCH(), OFFSET() and SUM(). See the example file attached.

 

In January and February that it doesn't work.

whoa....... i would never have gotten there on my own... THANK YOU!!

@PRisman 

If you want to avoid OFFSET...

_Screenshot.png

in E2:

=SUM(INDEX(C2:C13, MATCH(E1,B2:B13,0)-2):INDEX(C2:C13, MATCH(E1,B2:B13,0)))

or, with Excel 2021/365:

=LET(
    m, XMATCH(E1,B2:B13),
    SUM(INDEX(C2:C13,m-2):INDEX(C2:C13,m))
)