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]

Awesome thank you.

 

[jomiscli]

SRCV2.xlsm
	A	B	C	D
1	AssemblyID	Name	Type	Rarity
2	1	Adaptive Frame	Manufactured Component	Common
3	2	Aldumite Drilling Rig	Manufactured Component	Unique
4	3	Austenitic Manifold	Manufactured Component	Uncommon
5	4	Comm Relay	Manufactured Component	Uncommon
6	5	Control Rod	Manufactured Component	Exotic
Assemblies

SRCV2.xlsm
	A	B	C
1	SubassemblyID	AssemblyID	Name
2	101	5	Austenitic Manifold
3	101	27	Austenitic Manifold
4	102	2	Drilling Rig
5	103	28	Indicite Wafer
Subassemblies

SRCV2.xlsm
	A	B	C	D
1	MaterialID	Name	Type	Rarity
2	201	Adhesive	Organic	Rare
3	202	Aldumite	Inorganic	Unique
4	203	Alkanes	Inorganic	Uncommon
5	204	Aluminum	Inorganic	Common
Materials

SRCV2.xlsm
	A	B	C	D
1	AssemblyID	SubassemblyID	MaterialID	Quantity
2	1		204	1
3	1		236	1
4	2		202	3
5	2		215	2
6	2	102		1
7	2	107		1
8	3		236	2
9	3		247	2
10	3	112		1
Assembly_Materials

So this is what I came up with as far as storing the data. I feel like its ok, but the duplicates on the subassembly sheet might not need to be there.

Any opinions on this would be awesome. If you want me to paste anymore data let me know!