Sum Formulas

A

Angela

I have data where it is sorted by dated and I wanted to subtotal the number
of plants that we delivered for a specific date. The problem is the exported
data has numerals and text. (Example below.) So when I try and do a simple
sum it isn't. Can someone help me with how to get it to just sum the number.
I believe for the most part it is just the 1st or 1st and 2nd characters of
the field. I thought I read somewhere that you can just pull the numbers out
but as in the 1st example the pot size is showing up as well and I don't want
that to be included.

16,Pothos Jade Green 06"
2,Aglaonema Silver Queen

Thank you in advance,

Sincerely
Angela
 
P

PegL

There are several options. Assuming you need to keep the existing cells "as
is", here's a way to sum the #s.
=SUM(VALUE(LEFT(A2:A5,FIND(",",A2:A5,1)-1)))
Change both a2:a5 instances to your range, and press Alt+Shift+Enter to make
this an array formula.
 
J

JE McGimpsey

One way:

Choose Data/Text to Columns/Delimited. Choose comma as the separator.
 
P

Peo Sjoblom

Easiest way would be to insert a new column to the right, then select the
range you want to sum and do data>text to columns, select delimited and
click next, then select comma and click finish. Now just do a simple sum


if you insist using bad spreadsheet design use

=SUM(IF(ISNUMBER(FIND(",",A2:A10)),--SUBSTITUTE(A2:A10,MID(A2:A10,FIND(",",A2:A10),255),"")))

needs to be entered with ctrl + shift & enter


replace A2:A10 with your range
 
Top