Xlookup then summing prior columns? Example attached

Buzby

New Member
Joined
Feb 22, 2025
Messages
7
Office Version
  1. 365
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?
 

Attachments

  • 1000040273.jpg
    1000040273.jpg
    152.7 KB · Views: 9
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.
 
Upvote 0
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.
 
Upvote 0
I just changed it
Thanks for updating. (y)

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
ABCDEFG
3Q1 2024 LTMQ2 2024 LTM
4Apples500550
5Pears450400
6Oranges690990
7
12
13Q1 2023Q2 2023Q3 2024Q4 2024Q1 2024Q2 2024
14Apples100100100100200150
15Oranges200300150140100600
16Pears30010015010010050
Sum qtrs
Cell Formulas
RangeFormula
B4:C6B4=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)))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Solution
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top