98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » X LOOKUP and SUM
X LOOKUP and SUM
Resolved · Medium Priority · Version 365
Thu 2 Jan 2025, 17:32Delegate Elizabeth said...
Elizabeth has attended:
Excel Advanced course
Excel Intermediate course
X LOOKUP and SUM
Is there something that I can use that is like XLOOKUP but can sum items in the sheet it is extracting data from?
For example, I want to use the total value of payments made to each of a list of suppliers. I have a file that has all the payments to all suppliers.
So ideally I would like to ask Excel to use the supplier reference in Sheet A and go to Sheet B and find all the entries with Supplier A and return the total value of payments to A and the same for B and so on.
Please can you suggest a way to do this before I resort to a manual approach.
Thanks,
Elizabeth
Fri 3 Jan 2025, 09:28Trainer Jens said...
RE: X LOOKUP and SUM
Hi Elizabeth,
Thank you for the forum question.
You will need to use a SUMIFS function.
On sheet B create a list with all suppliers names. If you have the list in column A with the first name in A2 and you want the total for each supplier in column B, and let us imaging that you have the values you want to sum in a sheet called A and in column C starting from C2 going to C100 and you have all the suppliers in column B (from B2 to B100)
Then the SUMIFS will look like this.
=SUMIFS((A!$C$2:$C$100,A!$b$2:$B$100,A2)
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Ctrl+d's double lifeSuppose I have a formula in B1 that I wish to copy into B2:B10. I can select B1:B10 then press Ctrl+d to copy the formula down the selected range. Users generally ignore this shortcut in favour of double-clicking on the fill handle to copy down, but Ctrl+d is useful sometimes particularly when there is no data in surrounding columns to guide to how far the double-click method should copy formulae. |
