Camilla has attended:
Excel Advanced course
Excel Advanced course
Xlookups
Hello!
I have a spreadsheet which contains multiple sheets and each sheet is set up the same but contains weekly sales data.
I then need to add up the 'sold' columns for each week at the end of the month, but the problem is that the sheets get sorted so they don't always match.
So as an example I have a product code xx12 and I've got a column for how many pieces we sold last week and the previous week, and so on. I also have xx13 and xx14 and so on. So when I add up last week's quantity sold with the previous weeks I want to add it up for the correct product code. So essentially what I'm doing is an xlookup + xlookup, etc. but it keeps giving me an error.
Could I have some assistance on how to best do this please?
RE: Xlookups
Hi Camilla,
Thank you for the forum question.
Here’s a clean way to solve this without errors—and without having to chain multiple XLOOKUPs together.
You have weekly sheets with identical structure, each containing product codes and sold quantities. Because the sheets get sorted, you can’t rely on row positions—only product codes.
Goal: Sum the “Sold” values for the same product code across multiple weekly sheets.
________________________________________
Best, Most Reliable Solution: SUM multiple XLOOKUPs
If the product code is in A2 on your summary sheet (or end of month sheet), and each weekly sheet has:
• Product codes in column A
• Sold values in column B
Then use:
=SUM(
XLOOKUP(A2, Week1!A:A, Week1!B:B, 0),
XLOOKUP(A2, Week2!A:A, Week2!B:B, 0),
This avoids errors because XLOOKUP returns 0 if a match is not found (due to the last parameter).
If your sheets are named "Wk1", "Wk2", "Wk3", etc., adjust accordingly.
________________________________________
Even Cleaner: SUMIFS Across Sheets (If Possible)
Excel does not allow 3D SUMIFS directly, but if each sheet cell is in the same position, you could use traditional 3D references:
=SUM(Wk1:Wk4!B2)
…but since your sheets are sorted differently, this will NOT work for you.
________________________________________
Best Scalable Method: Use a 3D INDIRECT solution
If your weekly sheet names are listed somewhere (example: E1:E4 contains Wk1, Wk2, Wk3, Wk4), use:
=SUMPRODUCT(
XLOOKUP(
A2,
INDIRECT("'" & $E$1:$E$4 & "'!A:A"),
INDIRECT("'" & $E$1:$E$4 & "'!B:B"),
0
)
)
Automatically sums across however many week sheets you add
No need to edit formulas each time you add a week
Independent of sheet sorting
________________________________________
If you are getting errors now, it’s most likely because:
At least one XLOOKUP is returning #N/A
Fix: Provide a default value (e.g., 0):
XLOOKUP(A2, Week1!A:A, Week1!B:B, 0)
Your sheets contain text instead of numbers
Fix: Wrap values in VALUE() or check formatting.
You're summing blank or error producing weekly values
Fix: SUMPRODUCT + XLOOKUP avoids this problem.
________________________________________
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
