Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
I have several columns (formatted as text) containing numbers with
dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
Hi Randi,
Assuming your data is in A1:A4 In B1 put =SUM(LEFT(A1:A4,2)*1) and in C1 put =SUM(RIGHT(A1:A4,1)*1) Both formulae are array formulas so must be committed with Ctrl+Shift+Enter and not just enter HTH Martin "Randi" wrote in message ... I have several columns (formatted as text) containing numbers with dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
These formulas should work:
For numbers to the left of the -: =SUMPRODUCT(--(LEFT(A1:A10,FIND("-",A1:A10)-1))) For numbers to the right of the -: =SUMPRODUCT(--(MID(A1:A10,FIND("-",A1:A10)+1,99))) Adjust the range A1:A10 to meet your needs. HTH, Elkar "Randi" wrote: I have several columns (formatted as text) containing numbers with dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
Thank you - that works - but now I need something different. How can I
change that formula to multiple the number to the left of the dash by the number to the right of the dash, i.e., 40-2 = 80 and thenadd up the totals? thanks "MartinW" wrote: Hi Randi, Assuming your data is in A1:A4 In B1 put =SUM(LEFT(A1:A4,2)*1) and in C1 put =SUM(RIGHT(A1:A4,1)*1) Both formulae are array formulas so must be committed with Ctrl+Shift+Enter and not just enter HTH Martin "Randi" wrote in message ... I have several columns (formatted as text) containing numbers with dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
Do you import these values, if not why are you using what is a really bad
layout? The best way would be to make sure the column to the right is empty, then select the column with these values and do data text to columns, select delimited and click next, then as delimiter select - and then click finish, then simply use =SUMPRODUCT(A1:A5,B1:B5) Using Elkar's formula =SUMPRODUCT(--(LEFT(A1:A10,FIND("-",A1:A10)-1)),--(MID(A1:A10,FIND("-",A1:A10)+1,99))) however if there is a value without a dash or an empty cell it will return an error -- Regards, Peo Sjoblom "Randi" wrote in message ... Thank you - that works - but now I need something different. How can I change that formula to multiple the number to the left of the dash by the number to the right of the dash, i.e., 40-2 = 80 and thenadd up the totals? thanks "MartinW" wrote: Hi Randi, Assuming your data is in A1:A4 In B1 put =SUM(LEFT(A1:A4,2)*1) and in C1 put =SUM(RIGHT(A1:A4,1)*1) Both formulae are array formulas so must be committed with Ctrl+Shift+Enter and not just enter HTH Martin "Randi" wrote in message ... I have several columns (formatted as text) containing numbers with dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
Hi Randi,
This will work for your simplified example =SUMPRODUCT(--(LEFT(A1:A4,2)*(--(RIGHT(A1:A4,1))))) However I think it may be better to look at separating your data with text to columns and then working from there. HTH Martin "Randi" wrote in message ... Thank you - that works - but now I need something different. How can I change that formula to multiple the number to the left of the dash by the number to the right of the dash, i.e., 40-2 = 80 and thenadd up the totals? thanks "MartinW" wrote: Hi Randi, Assuming your data is in A1:A4 In B1 put =SUM(LEFT(A1:A4,2)*1) and in C1 put =SUM(RIGHT(A1:A4,1)*1) Both formulae are array formulas so must be committed with Ctrl+Shift+Enter and not just enter HTH Martin "Randi" wrote in message ... I have several columns (formatted as text) containing numbers with dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding columns containing numbers with dashes
Thanks. I am a brand new user to Excel - hence the poor layout you've
referred to. Thanks for your halp. "Peo Sjoblom" wrote: Do you import these values, if not why are you using what is a really bad layout? The best way would be to make sure the column to the right is empty, then select the column with these values and do data text to columns, select delimited and click next, then as delimiter select - and then click finish, then simply use =SUMPRODUCT(A1:A5,B1:B5) Using Elkar's formula =SUMPRODUCT(--(LEFT(A1:A10,FIND("-",A1:A10)-1)),--(MID(A1:A10,FIND("-",A1:A10)+1,99))) however if there is a value without a dash or an empty cell it will return an error -- Regards, Peo Sjoblom "Randi" wrote in message ... Thank you - that works - but now I need something different. How can I change that formula to multiple the number to the left of the dash by the number to the right of the dash, i.e., 40-2 = 80 and thenadd up the totals? thanks "MartinW" wrote: Hi Randi, Assuming your data is in A1:A4 In B1 put =SUM(LEFT(A1:A4,2)*1) and in C1 put =SUM(RIGHT(A1:A4,1)*1) Both formulae are array formulas so must be committed with Ctrl+Shift+Enter and not just enter HTH Martin "Randi" wrote in message ... I have several columns (formatted as text) containing numbers with dashes, i.e., 20-1 15-2 30-4 05-3 Is there a way to add up the numbers separately in the column, i.e, one total for the 20, 15, 20, 05 and one total for the 1, 2, 4, 3. If unable to add both is it possible to add up just the numbers to the left of the dash? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Dashes to a 25 digit alphanumeric cell | Excel Discussion (Misc queries) | |||
Adding and subtracting many columns numbers | Excel Worksheet Functions | |||
Convert phone numbers with dashes in them to just numbers | Excel Discussion (Misc queries) | |||
ADDING NUMBERS IN COLUMNS ACROSS IN EXCEL | Excel Worksheet Functions | |||
problem with adding columns of numbers | Excel Worksheet Functions |