Sum range of numbers within a range of cells separated by /

Hello,

I have very basic knowledge of excel and I could use some assistance finding a complicated solution to what should be a easy one. I have spent a number of hours over the past few days searching your vast and informative forums with no avail. (Love the forums by the way)

I have monthly data like this: Untitled.png

____Company A1______Company A2________Brand A

Jan____11/10__________15/15____________26/25
Feb____5/5_____________7/6____________12/11
Mar___ 0/0_____________2/1_____________2/1
Apr
May
....
Dec
Total__16/15___________24/22___________40/37


I am trying to sum numbers vertically and horizontally as shown in the Brand A column and Total row. Now I know the easiest solution would be to break the data into separate columns, the problem is that this is part of a bigger table and I need to maintain the structure of the table. I found a messy way of collecting the horizontal Brand A data, but it only works when all cells are populated.

For the Total row, I tried this formula;

Formula:

Please Login or Register  to view this content.

Unfortunately it also does not work with empty cells and I need to see the running total.

So if somebody could assist with finding a way to clean up the Horizontal brand data and calculate the running totals row, it would be greatly appreciated. File attached.Helpbook1.xlsx

Thank you in advance.

wrong formula posted

In I 37 should work
=SUMPRODUCT(IFERROR(LEFT(SUBSTITUTE(B37:D37,"/",REPT(" ",100)),50)+0,0))&"/"&SUMPRODUCT(IFERROR(RIGHT(SUBSTITUTE(B37:D37,"/",REPT(" ",100)),50)+0,0))

copy paste above then hold control and shift together and then hit enter and release all three keys !

Now this will work even if cell has no value or any cell has value

Maybe this one

and drag formula to the right

=SUM(--(LEFT(B2:B4,FIND("/",B2:B4)-1)))&"/"&SUM(--(RIGHT(B2:B4,LEN(B2:B4)-FIND("/",B2:B4))))

	B	C	D
2	11/10	15/15	26/25
3	5/5	7/6	12/11
4	0/0	2/1	2/1
5
6	16/15	24/22	40/37

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Thanks for the feedback ! if your query is solved you can mark thread as solved.
GO to thread tools and mark as solved !