Excel - Summing Multiple Material IDs Cost from one Cell

Question

So, I have a Product that is made of multiple materials, labeled with material IDs. I need to pull the multiple material IDs from the cell and find their associated costs from another table, and then sum them all up to find the total production cost. This is easy to do when there is only one material needed for the product with a simple xlookup, however when there are multiple material IDs it doesn't work. This is for a little made up project where I'm starting a business selling jewelry. I apologize if the answer is very simple, I am not an Excel expert. Anyhow the help is appreciated, thank you!

I know I could work around this by first making a third table only for finding the production cost and having each material ID in its own cell, but I was really hoping I could find a way to avoid this and figure out how to xlookup and sum multiple materials based on their ID from one cell.

As you can see in cell K12 the necklace is made up of 3 different materials (three material IDs). How do I xlookup the three and sum them for the total production cost?

Here is a photo for reference:

And here is my code for the "Production Cost" column:

=SUM(XLOOKUP([@[Material IDs Needed]],Table1[Material ID],Table1[Material Cost]))

Black cat · Accepted Answer · 2024-04-28 16:03:58Z

1

This formula uses VLOOKUP and TEXTSPLIT. Replace ranges with columns' name reference if needed.

=SUM(VLOOKUP(VALUE(TEXTSPLIT(K4,",")),B$4:D$12,3,FALSE))

answered Apr 28, 2024 at 16:03

Black cat

7,6358 gold badges69 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yuyibruh Over a year ago

Thanks, I like using xlookup so I exchanged it but it worked perfectly thanks a lot. Here's what I have: =SUM(XLOOKUP(VALUE(TEXTSPLIT(K4,",")),Table1[Material ID],Table1[Material Cost]))

nkalvi · Accepted Answer · 2024-04-28 18:26:43Z

0

If you have Microsoft 365, you could define (Formulas > Define Name) functions for easier maintenance:

Define names:

fxGetIds =LAMBDA(ids, TEXTSPLIT(ids,","))
fxLookupCost =LAMBDA(id, XLOOKUP(VALUE(id),Table1[Marterial ID],Table1[Material cost],0))
fxCost =LAMBDA(ids,SUM(MAP(fxGetIds(ids), LAMBDA(id, fxLookupCost(id)))))

Then in Production Cost column

=fxCost([@[Material Ids Needed]])

answered Apr 28, 2024 at 18:26

nkalvi

2,6142 gold badges13 silver badges11 bronze badges

Collectives™ on Stack Overflow

Excel - Summing Multiple Material IDs Cost from one Cell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related