| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Left Side & Right Side Numbers
I'm hoping someone can help with this.
I'm trying to sum all the numbers on the left of a '/' and the the numbers on the right of the same. Example: 22/55 0/7 1/125 216/0 I trying to find a way to make the sumproduct provide the result of... 239/187. So far for the right side I have... =SUMPRODUCT(--(MID(R2:R4,FIND("/",R2:R4,1)+1,3))) .... But I can't seem to get the Left side to work. Thanks In Advance. Rob |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Left Side & Right Side Numbers
Assuming *every cell* contains the slash ( / )...
=SUMPRODUCT(--(LEFT(R2:R5,FIND("/",R2:R5)-1)))&"/"&SUMPRODUCT(--(MID(R2:R5,FIND("/",R2:R5)+1,3))) -- Biff Microsoft Excel MVP "Rob" wrote in message ... I'm hoping someone can help with this. I'm trying to sum all the numbers on the left of a '/' and the the numbers on the right of the same. Example: 22/55 0/7 1/125 216/0 I trying to find a way to make the sumproduct provide the result of... 239/187. So far for the right side I have... =SUMPRODUCT(--(MID(R2:R4,FIND("/",R2:R4,1)+1,3))) ... But I can't seem to get the Left side to work. Thanks In Advance. Rob |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Left Side & Right Side Numbers
=SUMPRODUCT(--LEFT(R1:R4,FIND("/",R1:R4)-1)) & "/" &
SUMPRODUCT(--(MID(R1:R4,FIND("/",R1:R4,1)+1,3))) -- Gary''s Student - gsnu201001 "Rob" wrote: I'm hoping someone can help with this. I'm trying to sum all the numbers on the left of a '/' and the the numbers on the right of the same. Example: 22/55 0/7 1/125 216/0 I trying to find a way to make the sumproduct provide the result of... 239/187. So far for the right side I have... =SUMPRODUCT(--(MID(R2:R4,FIND("/",R2:R4,1)+1,3))) ... But I can't seem to get the Left side to work. Thanks In Advance. Rob |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Left Side & Right Side Numbers
Hello,
Another way with a formula array (validate the formula with Ctrl+Shift+Enter instead with Enter) SUM(TRUNC(SUBSTITUTE(R2:R5,"/","."))) & "/" & SUM( -- MID(R2:R5, SEARCH("/",R2:R5)+1,99)) "Rob" wrote: I'm hoping someone can help with this. I'm trying to sum all the numbers on the left of a '/' and the the numbers on the right of the same. Example: 22/55 0/7 1/125 216/0 I trying to find a way to make the sumproduct provide the result of... 239/187. So far for the right side I have... =SUMPRODUCT(--(MID(R2:R4,FIND("/",R2:R4,1)+1,3))) ... But I can't seem to get the Left side to work. Thanks In Advance. Rob |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum Left Side & Right Side Numbers
On Thu, 18 Feb 2010 09:53:01 -0800, Rob wrote:
I'm hoping someone can help with this. I'm trying to sum all the numbers on the left of a '/' and the the numbers on the right of the same. Example: 22/55 0/7 1/125 216/0 I trying to find a way to make the sumproduct provide the result of... 239/187. So far for the right side I have... =SUMPRODUCT(--(MID(R2:R4,FIND("/",R2:R4,1)+1,3))) ... But I can't seem to get the Left side to work. Thanks In Advance. Rob =SUMPRODUCT(--LEFT(rng,FIND("/",rng)-1))&"/" & SUMPRODUCT(--MID(rng,FIND("/",rng)+1,99)) Be aware that this formula type (using FIND) will only work if All the entries are text All the entries contain a "/" --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| My excel spread sheet has numbers down left side and across top? | Excel Discussion (Misc queries) | |||
| how to make left side stay still and right side can move left to r | Excel Discussion (Misc queries) | |||
| change rows from left side to right side | Excel Discussion (Misc queries) | |||
| y-axis moves from the left hand side to the right hand side | Excel Discussion (Misc queries) | |||
| y-axis moves from the left hand side to the right hand side! | Charts and Charting in Excel | |||
Linear Mode
