Formula to get total number of multiple products

[Cristen123]

I am trying to get a formula to gather the total each piece of multiple products. For example, someone orders from us 10 of the option 1 grab bag and 10 of the option 3 grab bag. Both bags have 1 blue shirt, and 4 red shirts, the rest are different. How can I get the total of blue shirts, and the total of red shirts to give me the number I need to pull of each?

[#2]

@Cristen123 

ANSWER: Create an Excel Table that shows the contents of each grab bag in a row.

That's a general answer to a general question.

If you'd like more specific help, you'd help us help you by attaching a copy of whatever kind of spreadsheet you've got going so far. It would help to know how many other things are in each grab bag, what the maximum number of distinct components is now, is expected to be.

[#3]

@Cristen123 

Here is a link from Microsoft with the options for product summing.

Sum values based on multiple conditions

https://support.microsoft.com/en-us/office/sum-values-based-on-multiple-conditions-e610ae0f-4d27-480...

But if this is not what you are looking for----

With your permission, if I can recommend you, add a MS Excel file (without sensitive data) to your project.

Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

At the same time, it is much easier for someone who wants to help to understand the subject.

A win-win situation for everyone.

Please no picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#4]

Here is the file. Each SKU has a certain color of paint. We are trying to see how many of the same color we need when we receive orders for multiple kits.

[#5]

@Cristen123 

That set of spreadsheets may need no interpretation for YOU to understand it. You're asking quite a bit for an outsider, however, an outsider whom you've asked for help, to learn to navigate around the several sheets and make sense.

Would you be so kind as to give some kind of explanation for:

• the relationships between the "Paint Breakdown" sheet and the others, how to look at the one in light of the other, or is it vice versa??
• What's the relationship between the vertical and ;horizontal axes in the "Pain breakdown" sheet?
• How do the columns/rows on "Paint Breakdown" relate to the Kit Component sheets?
• And are the three "Kit Component" Sheets essentially subsets of one large set of SKUs--i.e., could they all be one sheet logically--or is there some other complex relationship between them, or none at all?
• Each SKU, you say, "has a certain color of paint." It looks more like each SKU has multiple colors of paint. Please explain which it is and again, how to make sense of it.

Please give some examples--other than red shirts and blue shirts-- as to the kind of answers you'd be looking for to go with typical orders of different Kits.

[#6]

@Cristen123 

As Mr. mathetes has already informed you, we need precise instructions / specifications on what to do / to help.

From the material and notes you have sent so far, I am unfortunately unable to follow the exact solution that you would like to achieve with Excel.

Nevertheless, I have adjusted the tables in your file a little where you can filter the whole table.

Hope this helps you.

If not, please provide detailed information, as Mr.mathetes  has already described.

Thank you for your patience and time.

Wish you a nice day / night with lots of health, joy and love.

Nikolino

I know I don't know anything (Socrates)

[#7]

@mathetes 

Thank you so much for your help so far! 

You are correct, each kit SKU has multiple different colors of paint.

We can have 1 tab with all information, or keep them separate. The Paint Breakdown tab is to give us the total amount of paints are used for each kit. For example, the first paint, Amber yellow, is in 5 different kits. 
 When we get an order for 50 of one, 30 of a different kit, and 20 of another, we are trying to figure out total, how many of each paint colors we will need. If each of those kits all take Amber yellow, for example, we would need a total of 100 Amber Yellow paints. 
Essentially, we would like to be able to enter how many total number of kits were ordered and the amount of paint bottles, for each color, will be needed. 

Does this help any? 

Thank you so much for your help! 

[#8]

@Cristen123 

Here's a partial solution. You will need to have the most recent version of Excel the way this is written, since it uses FILTER

You'll see a new sheet (a number of new sheets in fact), but a new sheet where you can place the orders by kit name. For multiple orders, varying quantities of different kits, you will see the total number of each paint that the total order calls for. I think that's what you want.

With this solution, I was only able to make it work when you begin with the Kit NAME; for some reason I haven't been able to figure out, I could not make the SKU work. I'm going to assume that your customers are more likely to use product/kit name, so this will actually be more functional.

There is some data clean-up needed, I think, maybe one or two SKUs are re-used when they should be distinctive. I'm going to leave all that for you to take care of. My goal was just to show a way to accomplish what you were asking. As you'll see, I trust, a single table is actually the key to being able to extract the data you were wanting to get.