Forum Discussion
Formula to get total number of multiple products
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.
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.
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!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.
