I have what should be an easy question...and yet... Let's say I have a data table as follows: Jan56Feb92Mar48Apr87May66Jun99Jul78Aug83Sep69Oct72Nov81Dec59 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?

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.

Join us June 17–18 for a deep dive into Copilot Control System—live expert-led sessions and Q&A on data security, agent lifecycle, adoption, and more! Learn more >

Forum Discussion

PRisman

Copper Contributor

Aug 10, 2022

Solved

Sum values across ROWS in LookUp

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

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

Jan	56
Feb	92
Mar	48
Apr	87
May	66
Jun	99
Jul	78
Aug	83
Sep	69
Oct	72
Nov	81
Dec	59

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?

Formulas and Functions

dscheikey
Aug 10, 2022
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.
Sum values across ROWS in LookUp.xlsx9 KB

3 Replies

Lorenzo

Silver Contributor

Aug 10, 2022

PRisman

If you want to avoid OFFSET...

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))
)

dscheikey
Bronze Contributor
Aug 10, 2022
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.
Sum values across ROWS in LookUp.xlsx9 KB
- PRisman
  Copper Contributor
  Aug 10, 2022
  whoa....... i would never have gotten there on my own... THANK YOU!!

Jan	56
Feb	92
Mar	48
Apr	87
May	66
Jun	99
Jul	78
Aug	83
Sep	69
Oct	72
Nov	81
Dec	59

Jan	56
Feb	92
Mar	48
Apr	87
May	66
Jun	99
Jul	78
Aug	83
Sep	69
Oct	72
Nov	81
Dec	59

Jan	56
Feb	92
Mar	48
Apr	87
May	66
Jun	99
Jul	78
Aug	83
Sep	69
Oct	72
Nov	81
Dec	59