Xlookup then summing prior columns? Example attached

[Buzby]

Hi,

I'm trying to create a summary sheet where it looks at a data sheet for an item and a period, then sums up the previous 3 quarterly periods plus the current one ie Last twelve months

So here id need to formula in cell B4 to get the total 500.

I have been using sum product and specifying the columns to look at but the columns in the dataset have the potential to move so I want to ensure that the calculation of prior 3 quarters + specified quarter, for that product, always works.

Is there a better way? Maybe offset?

 

[Peter_SSs]

Welcome to the MrExcel board!

Office version: 'Prefer Not To Say' is not very helpful to people here trying to help you since different versions have different functions and features available to them. I also can't see that your Excel version(s) is something that would need to be kept private so perhaps you could consider changing that. It will generally get you suitable responses more quickly.

 

[Buzby]

Hi Peter, sorry, I just changed it as im doing this post on my phone and wasn't sure what version I had! I'm assuming it's the latest one, 365.

 

[Peter_SSs]

I just changed it

Thanks for updating.

Maybe offset?

You could use OFFSET but that is a volatile function so I would mostly avoid that when other options exist. Is this the sort of thing you want?

25 02 22.xlsm
	A	B	C	D	E	F	G
3		Q1 2024 LTM	Q2 2024 LTM
4	Apples	500	550
5	Pears	450	400
6	Oranges	690	990
7
12
13		Q1 2023	Q2 2023	Q3 2024	Q4 2024	Q1 2024	Q2 2024
14	Apples	100	100	100	100	200	150
15	Oranges	200	300	150	140	100	600
16	Pears	300	100	150	100	100	50
Sum qtrs

Cell Formulas
Range	Formula
B4:C6	B4	=LET(c,MATCH(LEFT(B$3,7),$13:$13,0),r,MATCH($A4,$A$14:$A$16,0),SUM(INDEX($14:$16,r,c-3):INDEX($14:$16,r,c)))

 

[Buzby]

Hi, this looks amazing but do you mind explaining what each part does so I know for next time!

 

[Buzby]

Would i need to specify the cells 14 to 16 or can I just say the whole column? It's a big data sheet where the length can vary

 

[Peter_SSs]

Consider this formula in B4
=LET(c,MATCH(LEFT(B$3,7),$13:$13,0),r,MATCH($A4,$A$14:$A$16,0),SUM(INDEX($14:$16,r,c-3):INDEX($14:$16,r,c)))

This finds the final column for the relevant quarter (col 6) and stores it under the name 'c'

This finds the relevant row for the fruit (row 1 of the rows 14:16) and stores it under the name 'r'

This adds the relevant row from the column 3 to the left of the final column up to the final column

Would i need to specify the cells 14 to 16 or can I just say the whole column?

I would not use the whole columns. Just make a range big enough to be sure to cover your data.
For example, if you will have a few thousand rows you could use, say, $14:$10000

 

[Buzby]

Thank you so much, this looks like it will work but will try!

 

[Buzby]

Hi - im not sure whether a new post is required but is there a way to rank this data above on the summary form rather than on the data sheet itself based on say Q4 2024. Or would i have to rank on the data sheet and do an xlookup on this data. So in the example, id want to see that Apples are ranked number 1 across all fruits and vegetables

 

[Peter_SSs]

If this is not what you want then please prepare a small set of dummy data, fill in the required results manually and post that with XL2BB. then explain again in relation to the new sample data.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Buzby.xlsm
	A	B	C	D	E	F	G
1
2					Q2 2024 LTM
3		Q1 2024 LTM	Q2 2024 LTM		Rank
4	Apples	500	550		Oranges
5	Pears	450	400		Apples
6	Oranges	690	990		Pears
7
12
13		Q1 2023	Q2 2023	Q3 2024	Q4 2024	Q1 2024	Q2 2024
14	Apples	100	100	100	100	200	150
15	Oranges	200	300	150	140	100	600
16	Pears	300	100	150	100	100	50
Sheet3

Cell Formulas
Range	Formula
B4:C6	B4	=LET(c,MATCH(LEFT(B$3,7),$13:$13,0),r,MATCH($A4,$A$14:$A$16,0),SUM(INDEX($14:$16,r,c-3):INDEX($14:$16,r,c)))
E4:E6	E4	=SORTBY(A4:A6,INDEX(B4:C6,0,MATCH(E2,B3:C3,0)),-1)
Dynamic array formulas.