Formula help

kaskady

New Member
Joined
May 26, 2018
Messages
8
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 ?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
=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..... " ; "
 
Upvote 0
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.
 
Upvote 0
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 "
 
Upvote 0
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.
 
Upvote 0
Yes, that's my backup plane if I don't solve the problem with the formula. Thanks for the effort, Tony.
 
Upvote 0
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)))
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top