Text and Numbers in a cell

[jomiscli]

So I have been trying to use a SUMIFS formula to calculate stuff but I keep returning 0 no matter how much I try. My only thought now is that the numbers inside the cell with the text are not being considered numbers. Here is an example:

Wood:2, Stone:1, Metal:5

Does Excel automatically make each data type what it is? Or does it just automatically make is all text?

 

[jomiscli]

Thank you for the formula and responses.

Yeah getting the data right is something I am having trouble with.

My end goal is to have a seperate sheet from the main data table that would have a column of checkboxes for all the crafted items, and next to it, a textbox or some type of way to define a quantity for that item. Below that I want a button that when pressed would be able to compile a list of all ingredients required to craft the selection. As an example if I chose 100 Wooden Shields that needed 5 wood each the list would output 500 wood. I have had some iterations where I could get this to work with items that only require other items that are not crafted, but as soon as I need crafted items to also be used as ingredients I am finding it hard to properly extract the information. An example would be a tier 4 crafted item needing a tier 1, 2, and 3 crafted item to be made. Ideally I would want the list that gets compiled to be able to extract resources for all the items selected, as well as the sub recipes.

In your examples Jon I am currently using a variation of the second alternative. The first column are all items, crafted and raw. The second is type, the third is rarity, and the fourth is an ingriedients and quanity header that has 8 sub columns that list each ingredient, and each quantity in their own cells, like this:

​

Ingredients and Quantity​

Wood

5

Wood

2

Stone

1

Metal

5

Wooden Shield

1

The first row would represent the Wooden Shield recipe, and the one below it would represent an Iron Shield recipe. I can't figure out how to define the Wooden Shield within the Iron Shield recipe. I have redone the table multiple different times and now that I am unable to really get this version going I am feeling like the data could be stored more efficiently in a different way.

I have had some success but utimately hit a road block here. The VBA and formulas do seem to be a little big and unwieldy when I feel like they probably shouldn't be. Not to mention they haven't fully worked.

 

[awoohaw]

are you saying you want an ingredient list of one item to be a component of a second?
If so, will all the "sub component items" be listed above the main item?

Finally, why not just include the base items in the main item and skip the sub component itemization?
Have a chart like this:

Crafted Item	Ingredient	Number
Wood Shield	Wood	5
Iron Shield	Wood	7
Iron Shield	Stone	1
Iron Shield	Metal	5

 

[Jon Peltier]

Thank you for the formula and responses.

Yeah getting the data right is something I am having trouble with.

My end goal is to have a seperate sheet from the main data table that would have a column of checkboxes for all the crafted items, and next to it, a textbox or some type of way to define a quantity for that item. Below that I want a button that when pressed would be able to compile a list of all ingredients required to craft the selection. As an example if I chose 100 Wooden Shields that needed 5 wood each the list would output 500 wood. I have had some iterations where I could get this to work with items that only require other items that are not crafted, but as soon as I need crafted items to also be used as ingredients I am finding it hard to properly extract the information. An example would be a tier 4 crafted item needing a tier 1, 2, and 3 crafted item to be made. Ideally I would want the list that gets compiled to be able to extract resources for all the items selected, as well as the sub recipes.

In your examples Jon I am currently using a variation of the second alternative. The first column are all items, crafted and raw. The second is type, the third is rarity, and the fourth is an ingriedients and quanity header that has 8 sub columns that list each ingredient, and each quantity in their own cells, like this:

​

Ingredients and Quantity​

Wood

5

Wood

2

Stone

1

Metal

5

Wooden Shield

1

The first row would represent the Wooden Shield recipe, and the one below it would represent an Iron Shield recipe. I can't figure out how to define the Wooden Shield within the Iron Shield recipe. I have redone the table multiple different times and now that I am unable to really get this version going I am feeling like the data could be stored more efficiently in a different way.

I have had some success but utimately hit a road block here. The VBA and formulas do seem to be a little big and unwieldy when I feel like they probably shouldn't be. Not to mention they haven't fully worked.

For almost any kind of subsequent analysis (Pivot Tables or formulas) my last alternative is preferable. While your horizontally arranged list has a comfortable one-row-per-item layout, it is harder to extract data from. And it would be possible to start with the preferred data layout, and generate any number of pivot tables that may include a one-row-per-item summary as well as a general total of materials required.

When you have the possibility of subassemblies, you might include another column to capture that:

 

[awoohaw]

For almost any kind of subsequent analysis (Pivot Tables or formulas) my last alternative is preferable. While your horizontally arranged list has a comfortable one-row-per-item layout, it is harder to extract data from. And it would be possible to start with the preferred data layout, and generate any number of pivot tables that may include a one-row-per-item summary as well as a general total of materials required.

When you have the possibility of subassemblies, you might include another column to capture that:

View attachment 100880

Jon, it seems to me a Wooden Shield would be a sub component of the Iron Shield? Maybe something like this. Not sure how it would work in a pivot, but for look ups it would need to be listed as an Item before it is listed as a component.

In your example, I don't understand how you will get the Wooden Shields 5 pieces of wood wrapped into the Iron Shield formula.

Crafted Item	Component	Ingredient	Count
Wooden Shield		Wood	5
Iron Shield		Metal	5
Iron Shield		Stone	1
Iron Shield	Wooden Shield		1

 

[Jon Peltier]

Jon, it seems to me a Wooden Shield would be a sub component of the Iron Shield? Maybe something like this. Not sure how it would work in a pivot, but for look ups it would need to be listed as an Item before it is listed as a component.

In your example, I don't understand how you will get the Wooden Shields 5 pieces of wood wrapped into the Iron Shield formula.

Crafted Item	Component	Ingredient	Count
Wooden Shield		Wood	5
Iron Shield		Metal	5
Iron Shield		Stone	1
Iron Shield	Wooden Shield		1

I thought about this for a few moments while formulating my reply. Since I don't really know how the data is going to be used, I decided I couldn't spend too much time trying to build the most efficient system, and what I suggested reflects that. In fact, a relational database might work best, where you can find the ingredients of a subassembly and include them in the total.

 

[jomiscli]

awoohaw, yes I would like to be able to represent the item list of one component as a component of a second.

I really think the examples you guys have giving me in the last couple posts will really make the difference.

Thank you guys. I'll post back here with results once I get them going.

 

[awoohaw]

I thought about this for a few moments while formulating my reply. Since I don't really know how the data is going to be used, I decided I couldn't spend too much time trying to build the most efficient system, and what I suggested reflects that. In fact, a relational database might work best, where you can find the ingredients of a subassembly and include them in the total.

agreed a relational db is a great use case, or even using power query with relationships/links are a good idea as well.

I think, so it is possibly incorrect he wants to get an ingredient list to make something. He wants to select an item and how many to build and then get a complete ingredient list. Much like the recipe apps on line (like allrecipes.com) have for scaling up or scaling down an item to cook. I just think the intermediate step of using another prepared item in the recipe is unnecessary.

 

[jomiscli]

Hmmm. Well now I think I am going to do a little research on Power Query and relational data bases!

 

[jomiscli]

So I have made two seperate tables so far. One that lists all base raw materials, and another that lists all manufactured components. Would that be enough to do what I need, or would I need a table for crafted items that have subassemblies as well?

 

[awoohaw]

So I have made two seperate tables so far. One that lists all base raw materials, and another that lists all manufactured components. Would that be enough to do what I need, or would I need a table for crafted items that have subassemblies as well?

the sub-assemblies would need to be in both tables, I think.
There are many youtube channels that teach power pivot.
Leila Gharani, MyOnlineTrainingHub, Mr. Excel, and ExcelIsFun

Take a look at this playlist:

https://www.youtube.com/playlist?list=PLrRPvpgDmw0k_aIFiiIgRufZ8nmB6iyr-