# Summing Mixed Text and Number Column

Greetings. New to Smartsheets. First time question. I have form entries that looks like this:

333 . 11111 - Parts

333 . 22222 - Service

I want to sum all entries from the form that begin with "333" and I cant seem to make it work. I dont want to divide my columns. I'm currently using a SUMIF formula and playing with a FIND and @cell for the criterion piece. The answer is going back and forth between 0 and #UNPARSEABLE, depending on how I play with it. Does anyone have any insight? Please and thank you!

«1

• ✭✭✭✭✭✭

Are you trying to add ?

333.11111 + 333.22222 = 666.33333

Or are you trying to count that there are two entries beginning with 333?

• Oops. Sorry for not being clear...Im trying to do a SUMIF on numbers that are on another column that go with these codes. So if, for instance:

333 . 1111 Parts = 50

333 . 2222 Service = 100

• ✭✭✭✭✭✭

Try this...

=SUMIFS([Summing Column]:[Summing Column], [Listing Column]:[Listing Column], CONTAINS("333", @cell))

• =SUMIFS([Amount]:[Amount], [Purchase Coding]:[Purchase Coding], CONTAINS("241", @CELL))

Its still not working :(

• Also....I just noticed that the Excel file I used to create the form data has a space in front of the first characters...Will this affect the contains search?

• ✭✭✭✭✭✭

"Not working"... Is it an incorrect count or an error?

• Incorrect count

• ✭✭✭✭✭✭

Is it pulling some or none?

• Its pulling a zero

• ✭✭✭✭✭✭

Exactly how is the data in your [Purchase Coding] column being populated?

• From a form entry. The amount box is formatted as a text / number on the form

• ✭✭✭✭✭✭

Ok. When users fill out the form, exactly what are they putting into each of those two fields? Are they including spaces in the Purchase Coding field similar to in your original post? Are they manually entering the \$ in the Amount field?

• They are choosing a drop down option for the purchase coding and they are manually entering a dollar amount. On my Excel file, that became the drop down menu, I have it formatted as such:

(space)241(space).(space)50642(space)-(space)Parts

I was trying to make this so that the form submitters could easily read the coding...did I mess myself up with this?

• ✭✭✭✭✭✭

Not necessarily. Just making sure I know exactly what kind of values we are working with.

For the Amount field... Do they just enter numbers, or do they actually enter the "\$" symbol?

• just numbers

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!