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 ?

 

[TonyUK72]

Untested - Put a space between the speech marks?

 

[kaskady]

The same result #VALUE !

 

[TonyUK72]

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

Recreated and inserted random spaces, your first formula works. I don't know why you are using semi colons though..... " ; "

 

[kaskady]

Yes, the first formula works until there is no blank cell in range B1:G1. But if there is one, the formula shows #VALUE ! Therefore I added --(B1:G1<>"") strings to the formula awaiting it will ignore blank cells, but it does not. If there is a blank cell, it shows #VALUE ! again.

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

I must use semi colons, if I use commas instead, excel does not accept it. I have Excel 2013.

 

[kaskady]

The problem is here:

--(B1:G1<>"1/1") formula works, ignores all cells containing 1/1
--(B1:G1<>"5/2") formula works, ignores all cells containing 5/2
--(B1:G1<>"") formula does not work, does not ignores blank cells, shows #VALUE !, the same result if apostrophe is used instead of "
--(B1:G1<>" ") formula does not work, does not ignores blank cells, shows #VALUE !, the same result if apostrophe is used instead of "

 

[TonyUK72]

Until someone comes up with a GENIUS solution a quick fix would be......

Select the entire range that contains your data, select FIND/REPLACE from the ribbon leave the FIND Field empty and the REPLACE Field 0/0, click OK.

This will ensure ALL areas of your data range contain a value an prevent the error from occurring, not a pretty solution.

 

[kaskady]

Yes, that's my backup plane if I don't solve the problem with the formula. Thanks for the effort, Tony.

 

[Worf]

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)))

 

[Aladin Akyurek]

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)