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?

 

[dreid1011]

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?

If you start typing as text, it will continue as text. Each cell can only be one data type, not mixed.

With that being said, are all the numbers the last part of the string preceded by ":"?

 

[jomiscli]

Yes they are.

Thank you for the reply!

 

[awoohaw]

Excel will think anycell with text in it is text. You cannot just add those cells together.
But, what you can do:
1. Is use text tools (textbefore and textafter) to parse out the numbers. You may need to use a "Unary" operator to convert numerals that are text into numbers

Mr excel questions 67.xlsm
	A	B	C
1
2	Wood:2	8
3	Stone:1
4	Metal:5
5
jomiscli

Cell Formulas
Range	Formula
B2	B2	=SUM(--(TEXTAFTER(A2:A4,":")))

2. If you want to manipulate and convert the data to individual columns use the TEXT TO COLUMNS tool.

The TextToColumns is on the DATA tab.​

​

​

​

​

​

​

 

[jomiscli]

I will definitely have to look at that Wizard.

Would something like this work:

Ingredients	Quantity
Wood, Stone, Metal	2, 1, 5

I was trying to limit the amount of rows/columns I needed because as my table grows the bottom starts to taper from increased ingredients and quantities.

 

[awoohaw]

I will definitely have to look at that Wizard.

Would something like this work:

Ingredients	Quantity
Wood, Stone, Metal	2, 1, 5

I was trying to limit the amount of rows/columns I needed because as my table grows the bottom starts to taper from increased ingredients and quantities.

I don't think this works. Your just making a text string of numerals and commas.
But, you can use the text split function on that quantity string to again convert those to numbers....

=SUM(--TEXTSPLIT(B10,","))

Mr excel questions 67.xlsm
	A	B	C
9
10		2,1,5	8
11
jomiscli

Cell Formulas
Range	Formula
C10	C10	= SUM(--TEXTSPLIT(B10,","))

 

[jomiscli]

Thank you for your responses.

I just decided to have a column named 'Ingredients and Quantity' with sub columns that will have each ingredient and each quantity in their own cells. This way when you look at a row in the 'Item' column everything will be stored in its own cell with the appropriate data type.

 

[Jon Peltier]

I was trying to limit the amount of rows/columns I needed because as my table grows the bottom starts to taper from increased ingredients and quantities.

The first rule in Excel is Get The Data Right. This applies when setting up a data range, performing an analysis, making a pivot table, charting data; anything in Excel.

The problem is that your approach makes analyzing the data incredibly difficult. I would think that your data would be easier to analyze if you had a column for each ingredient (i.e., columns for Wood, Stone, Metal, etc.) with quantities for each ingredient in the applicable column, or even better, one column for material, and another column for quantities, with multiple rows as needed for each item that uses a quantity of an ingredient.

Below I show what your approach seems like as well as my two alternatives.

 

[Rick Rothstein]

I am not sure where we are in this thread right now, but for your original post, if this is in cell A1...

Wood:2, Stone:1, Metal:5

then this formula will sum the numbers in that cell...

=SUM(IFERROR(0+TEXTSPLIT(A1,{":",","}),0))

 

[Jon Peltier]

One takeaway should be that weird data structures require weird formulas to evaluate.

How can the original layouts in this thread be analyzed in a pivot table? How can you get the total by ingredient from a column of such cells?