Sum with a slash "/"

  • Thread starter Thread starter Freshman
  • Start date Start date
F

Freshman

Dear experts,

I've columns in a workbook which, for an example, in a cell wrote as
"20/79". The users meaning is 20 persons order 79 tickets. As there are
several columns in this pattern and I don't want to split them into 3 columns
like "20", "/" and "79". Is there a way (formula) to sum up the figures on
the left hand side and the right hand side separately (column by column),
i.e. sum of left hand side of column A and sum of right hand side of column
A? Please advise.

Thanks in advance.
 
Change the ranges to match the cells where your data is...

Sum of Left side: =SUMPRODUCT(--LEFT(A1:A100,FIND("/",A1:A100)-1))

Sum of Right side: =SUMPRODUCT(--MID(A1:A100,FIND("/",A1:A100)+1,999))
 
Try the below

'sum of first part
=SUMPRODUCT(--REPLACE(A1:A5,FIND("/",A1:A5),99,))

'sum of second part
=SUMPRODUCT(--MID(A1:A5,FIND("/",A1:A5)+1,99))
 
Your life will be easier if you put your data into two columns.

You could use data|text to columns|delimited by the slash.

If you continue to use a single cell format, you may find that some entries get
converted to dates--and that could make it more painful.
 
I couldn't get Jacob's solution to work. I couldn't get Rick's solution to
work either. Freshman, if I were you, I'd select the range, then click Data
Text to Columns > Delimited > Next > in the Other box, put this /
Click to expand...
Click Finish and you should be done!
 
I tested my formulas before I posted them, so I know they work. My guess as to why you couldn't get them to work is that you set the range to include empty cells (the formulas were designed to work when all the cells in the range contained values). If you need to cover a larger range (which includes empty cells) than you have now (for future possible entries or removals), then use these formulas instead...

Leftside sum: =SUMPRODUCT(--LEFT(0&A1:A99&"/",FIND("/",0&A1:A99&"/")-1))

Rightside sum: =SUMPRODUCT(--MID(A1:A99&".0",FIND("/",A1:A99&"/.0")+1,999))
 
You are right, Rick. I included some blank cells in the range the first time
I tried it. I re-tried Jacob's solution; works great! I re-tired your first
solution, Rick; works great! The last one you posted is pretty sweet. I
just copied that one to my 'library'. That's a keeper!
 
Back
Top