Summing a column with numbers containing /

[Captain Lake]

I've a spreadsheet with data in 1 column listing internet download/upload speeds. So for example,

5/1
10/2
25/5

I'd like to sum the numbers in front of the /, and the numbers after the /, and keeping the same form, put the sum in one cell. In this example, it would sum to 40/8.

Is this possible?

Thanks for the help.

 

[Jeffrey Mahoney]

Will this work?

Book5
	G	H	I	J
3	5/1		40	8
4	10/2
5	25/5
Sheet1

Cell Formulas
Range	Formula
I3	I3	=SUM(VALUE(MID(G3:G5,1,SEARCH("/",G3:G5)-1)))
J3	J3	=SUM(VALUE(MID(G3:G5,SEARCH("/",G3:G5)+1,100)))

 

[Jeffrey Mahoney]

To combine those from the last post
=SUM(VALUE(MID(G3:G5,1,SEARCH("/",G3:G5)-1)))&"/"&SUM(VALUE(MID(G3:G5,SEARCH("/",G3:G5)+1,100)))

 

[Scott Huish]

=SUM(--TEXTBEFORE(A1:A3,"/"))&"/"&SUM(--TEXTAFTER(A1:A3,"/"))

 

[Captain Lake]

Thanks Jeff, yes, that works great!

I actually concatenated your 2 formulas into this =SUM(VALUE(MID(G3:G5,1,SEARCH("/",G3:G5)-1)))&"/"&SUM(VALUE(MID(G3:G5,SEARCH("/",G3:G5)+1,100))) to put it all in 1 cell

Double thumbs up to the thought in your signature, if only more people held this belief...

 

[Captain Lake]

=SUM(--TEXTBEFORE(A1:A3,"/"))&"/"&SUM(--TEXTAFTER(A1:A3,"/"))

Scott, what does the -- do?

 

[Captain Lake]

Scott, your formula works well also, however, some of my cells in the range are empty. Can we ignore them in the sum?

 

[Scott Huish]

Scott, what does the -- do?

It coerces the number in text form to an actual number.

 

[Scott Huish]

=SUM(--TEXTBEFORE(A1:A3,"/",,,,0))&"/"&SUM(--TEXTAFTER(A1:A3,"/",,,,0))

 

[Jeffrey Mahoney]

I didn't realize that. SO then we could take the VALUE out
=SUM(--(MID(G3:G5,1,SEARCH("/",G3:G5)-1)))&"/"&SUM(--(MID(G3:G5,SEARCH("/",G3:G5)+1,100)))