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
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
