[Solved] Add values with a slash

[Ryan56k]

Hello!

I'm not very good with figuring out the formulas and I hope you guys can help me out!

I have columns with numbers I need added together, however the numbers have a slash in between them.

Example: 300/245

How do I get the sum to add each side of the slash separately and display it as: "Total Sum/Total Sum"

I hope this made sense.

Thank you!
-Ryan

[FJCC]

If I understand you correctly, the easiest solution is to split the data into two columns. Select the cells and choose the menu item Data -> Text to Columns. Set the separator to Other and type / in the box next to that selection. Click OK and you will have two columns of data. At the beginning, the column next to your data should be empty. If it has data, click on its header, the letter giving its name, and then select Insert -> Columns to make a blank column.

[Ryan56k]

This actually does the opposite of what I want haha. I want it all in one column and then at the bottom of the one column I'd like it to add everything together and keep the format of "243/500". With the total of everything to the right of the slash being one value and everything to the left of the slash being one value.

[RoryOF]

Use the separate columns as FJCC suggests then put it back together in another column.

[FJCC]

I understood what you want, it just seems like asking for trouble to process data like that.
This formula seem to work if there is text of the correct pattern in every cell in the range A1:A6.

Code: Select all

=SUMPRODUCT(VALUE(LEFT(A1:A6;SEARCH("/";A1:A6)-1))) & "/" & SUMPRODUCT(VALUE(RIGHT(A1:A6;LEN(A1:A6) - SEARCH("/";A1:A6))))

[Ryan56k]

This is perfect! Thank you very much for going to the trouble and writing that out for me.

[Zizi64]

Or you can use the function SILLYSUM() as you can see in my attachment:

SillySum.ods

(15.03 KiB) Downloaded 67 times

It is a macro function. You can imporove it for more error handling or more features.

Here is the code:

Code: Select all

REM  *****  BASIC  *****

Option Explicit

Function SillySum(aRangeValues as variant)
 Dim Sum_0, Sum_1 as long
 Dim lPart_0, lPart_1 as long
 Dim sValue as variant
 Dim aParts as variant
 
	Sum_0 = 0 
	Sum_1 = 0
	For each sValue in aRangeValues
		if VarType(sValue) = V_STRING then
			aParts() = Split(sValue,"/")
			if ubound(aParts) > 1 then
				SillySum = "More than two data in a cell"
				exit function
			else
				Sum_0 = Sum_0 + val(aParts(0)
				Sum_1 = Sum_1 + val(aParts(1)
			end if
		elseif VarType(sValue) = V_EMPTY then
			' nothing to do		
		else
			SillySum = "NotAStringError"
			exit function
		end if
	next
SillySum = Sum_0 & "/" & Sum_1
end function