Sum from different sheet values

I need some advice on setting up the below please.

On Sheet 1, I have a list of items which can be chosen from a Data Validation List in Sheet 2

Each item in the list on Sheet 2 has a value.

I’d like to know how I can get the column in Sheet 1 to sum the total value of the chosen items in the list according to their values in the column next to them in Sheet 2.

In the example attached, the 4 items selected have a total of 6.

What formula or functions can I use to set this up?

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Try this:

=SUM(BYROW(A1:A4,LAMBDA(r,XLOOKUP(r,Sheet2!A2:A4,Sheet2!B2:B4))))

Many thanks, I'll try that, but I have uploaded a sample file if it helps.

Try it first, please - let me know.

Adapted for your sample sheet, which is different:

=SUM(BYROW(A2:A6,LAMBDA(r,XLOOKUP(r,Sheet2!A2:A8,Sheet2!B2:B8))))

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Originally Posted by AliGW

Try it first, please - let me know.

Yup looks like it works, many thanks

I added a bit more info to it as suggested by the instructions, but again thank you, I will check the attachment. I should be able to amend the ranges as I add more items, unless there's a better way to do this, but this works for now.

Pleae don't quote unnecessarily - it's just clutter.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.