XLOOKUP with SUM and FILTER

[Lawless83]

Afternoon Everyone,

Will try get all the info needed on the first post for everyone.

I am creating a spreadsheet that tracks service intervals on tools based on production data. I want the toolroom to be able to put in a week number when they service a tool and then a formula will sum up the production totals from a different worksheet from anything after that week number to calculate when the next service is needed.

The production data sheet works on tool numbers in the A column, week numbers across the top row and the production data in the resulting table.

I want the formula to use the cell the toolroom put in the week number of service as a reference value, and then sum up all values for that tool AFTER the declared week number.

I think I will need to use a XLOOKUP, along with FILTER and SUM but just cant seem to get it right. I have attached an example of the production data sheet. The "tooling" sheet where they would enter the week number of service would be a separate worksheet calling to this production data sheet.

Any help would be greatly appreciated

 

[p45cal]

One way:

=SUM(DROP(INDEX($B$2:$N$21,XMATCH($Q7,$A$2:$A$21),0),,XMATCH($R7,$B$1:$N$1)-1))

 

[Lawless83]

One way:

=SUM(DROP(INDEX($B$2:$N$21,XMATCH($Q7,$A$2:$A$21),0),,XMATCH($R7,$B$1:$N$1)-1))

View attachment 12081

You sir are a gentleman and a scholar. Much appreciated, that worked a treat....

But...

I have since encountered another issue, something I should have mentioned in my first post but I didn't realise it until putting in the formula you provided. On the data sheet we are working from data from 2025 and 2026. I attempted to rename all weeks from 25 as "Week 1 25" and "Week 2 25" and so on etc, and naming the 2026 weeks as "Week 1 26" etc etc but this has stopped your formula working, meaning it will only count until the end of 25 or will miss out 25 and just count 26.

I have edited the example to show what I mean in case Im not explaining myself properly. The example shows a selection of weeks, in reality its "week 1 25" to "week 52 25" and then "week 1 26" to "week 52 26"

I think this could be to do with the match_mode of the last xmatch in the formula, but cant seem to get the wildcards to work right.

Is this easily adaptable or is it an issue with the formatting of the data?

 

[p45cal]

I'm not sure what isn't working. In the attached I've added the same formula and added manually calculated totals; what should it be showing?