Sum Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
Back
Top