Hello. I need to sum cells with three different numbers in the each cell. The numbers are seperated by a /. For example A1 = 4/3/1 and A2 = 3/2/1. I need cell A3 to be 7/5/2. Please help.

✔ Best Answer

You could try this Array formula. It must be entered using Ctrl-Shift-Enter and there can not be any cells that don't contain values formatted like in your example. Blanks cells or cells that don't contain xx/yyy/z will cause a #VALUE error.

Once you enter it using Ctrl-Shift-Enter brackets { } will appear around it

=(SUM(LEFT(A1:A40,FIND("/",A1:A40)-1)*1)&"/")&

SUM(MID(A1:A40,FIND("/",A1:A40)+1,

FIND("/",A1:A40,FIND("/",A1:A40)+1)-

(FIND("/",A1:A40)+1))*1)&"/"&

SUM(RIGHT(A1:A40,LEN(A1:A40)-

FIND("/",A1:A40,(FIND("/",A1:A40)+1)))*1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

The easiest way would be to use the Data - Text To Columns feature and separate the numbers into their own cells (i.e. A1, B1, C1) and then use a formula like this: =SUM(A1:A2)&"/"&SUM(B1:B2)&"/"&SUM(C1:C2)

If the numbers must remain in A1 and A2, then this will work for your example since you only have single digit numbers:

=SUM(LEFT(A1,1),LEFT(A2,1))&"/"

&SUM(MID(A1,3,1),MID(A2,3,1))&"/"

&SUM(RIGHT(A1,1),RIGHT(A2,1))Once you start getting into variable lengths of numbers things get a bit more complicated, e.g.

A 1 34/213/1 2 1/43/266Now you have to introduce the FIND function to the locate the slashes and then extract the numbers between them.

Just to get the first SUM (35/) from those numbers, you'd need:

=SUM(LEFT(A1,FIND("/",A1)-1),LEFT(A2,FIND("/",A2)-1))&"/"

Extracting the middle digits requires an even more complicated combination of functions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I will need to use larger numbers (2 and 3 digits). Are you able to provide me with a complete formula to your example? Thanks for your help!

I also woud like to sum multiple (40) cells wth 3 number in each cell. Is there an easier way to do this than multiple formuals in a single cell? For example:

1 34/213/1

2 1/43/266

3 23/3/33

4 25/4/56

...(40)

Are you not willing to use the Data - Text To Columns feature, which will make all of this a lot simpler?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

You could try this Array formula. It must be entered using Ctrl-Shift-Enter and there can not be any cells that don't contain values formatted like in your example. Blanks cells or cells that don't contain xx/yyy/z will cause a #VALUE error.

Once you enter it using Ctrl-Shift-Enter brackets { } will appear around it

=(SUM(LEFT(A1:A40,FIND("/",A1:A40)-1)*1)&"/")&

SUM(MID(A1:A40,FIND("/",A1:A40)+1,

FIND("/",A1:A40,FIND("/",A1:A40)+1)-

(FIND("/",A1:A40)+1))*1)&"/"&

SUM(RIGHT(A1:A40,LEN(A1:A40)-

FIND("/",A1:A40,(FIND("/",A1:A40)+1)))*1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

That works perfectly. I was beginig to think it was nearly impossile to do what I was asking. I didn't want to change the spreadsheet with text to columns because it is not my spreadsheet to change. I just want to recommend an easier way of adding up the rows without a calculator.

Thanks for you help!!

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History