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