Summing Mixed Text and Number Column

Cristina Andersen

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!

Paul Newcome

When I went back to copy/paste your formula to make a change I noticed that @cell is in all caps @CELL. This needs to be all lowercase. Try fixing that first. If that doesn't work then try the below formula. If that doesn't work either then I will need to throw some mock data in a sheet to do some testing.

=SUMIFS([Amount]:[Amount], [Purchase Coding]:[Purchase Coding], FIND("241", @cell) > 0)

Paul Newcome

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?

Cristina Andersen

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

I want the return to be 150

Paul Newcome

Try this...

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

Cristina Andersen

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

Its still not working :(

Cristina Andersen

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?

Paul Newcome

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

Cristina Andersen

Incorrect count

Paul Newcome

Is it pulling some or none?

Cristina Andersen

Its pulling a zero

Paul Newcome

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

Cristina Andersen

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

Paul Newcome

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?

Cristina Andersen

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?

Paul Newcome

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?

Cristina Andersen

just numbers