Formula help

[kaskady]

Hi, everybody.

I have numbers like a/b, formatted as text, and I need to sum separately the numbers before the slash (a) and separately the numbers behind the slash (b) to get a result in the same a/b format. Example: 1/2 + 7/1 = 8/3. I have created a formula that works, but has problem with blank cells. If there is a blank cell in the specified range, the formula shows #VALUE !. The formula is:

=CONCATENATE(SUMPRODUCT(VALUE(LEFT(B1:G1;FIND("/";B1:G1)-1)));"/";SUMPRODUCT(VALUE(RIGHT(B1:G1;LEN(B1:G1)-FIND("/";B1:G1)))))

I tried to force it to ignore the blank cells by adding the --(B3:G3<>"") string like this:

=CONCATENATE(SUMPRODUCT(VALUE(LEFT(B3:G3;FIND("/";B3:G3)-1));--(B3:G3<>""));"/";SUMPRODUCT(VALUE(RIGHT(B3:G3;LEN(B3:G3)-FIND("/";B3:G3)));--(B3:G3<>"")))

but it shows #VALUE ! again.

Interesting is, when I change --(B3:G3<>"") to --(B3:G3<>"1/1") or any other number 2/2, 5/4 etc., then the formula works perfectly and ignores all cells containing the specified number. But with "" it does not work. What do I wrong ? Could someone help me to solve this please ?

 

[kaskady]

The following single cell array formula works for me:

=CONCATENATE(SUMPRODUCT(IF(LEN(B1:G1)>0;VALUE(LEFT(B1:G1;FIND("/";B1:G1)-1));0));"/";SUMPRODUCT(IF(LEN(B1:G1)>0;VALUE(RIGHT(B1:G1;LEN(B1:G1)-FIND("/";B1:G1)));0)))

Thanks for posting. Very good idea. And logic. But....for me it does not work. I really don't understand why. We use the same version of Excel. What do you think ? Could Win 10 be the problem ? I have Win 10.

 

[kaskady]

Either control+shift+enter, not just enter:

=SUM(LEFT(0&B1:G1,FIND("/",0&B1:G1&"/")-1)+0)&"/"&SUM(REPLACE(B1:G1,1,FIND("/",0&B1:G1&"/")-1,"0")+0)

Or just enter:

=SUMPRODUCT(LEFT(0&B1:G1,FIND("/",0&B1:G1&"/")-1)+0)&"/"&SUMPRODUCT(REPLACE(B1:G1,1,FIND("/",0&B1:G1&"/")-1,"0")+0)

Works excellent ! Thank you very much !

 

[kaskady]

Works excellent ! Thank you very much !

I just had to replace commas with semicolons.

 

[Aladin Akyurek]

I just had to replace commas with semicolons.

Right.

Works excellent ! Thank you very much !

You are welcome.