Forum Discussion

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 6...

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

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