Adding columns containing numbers with dashes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randi - 07 Sep 2007 14:34 GMT

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 to this Message

MartinW - 07 Sep 2007 16:52 GMT

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

>I have several columns (formatted as text) containing numbers with
> dashes, i.e.,
[quoted text clipped - 9 lines]
> to add both is it possible to add up just the numbers to the left of the
> dash? Thanks.

Reply to this Message

Randi - 07 Sep 2007 17:10 GMT

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

> Hi Randi,
>
[quoted text clipped - 20 lines]
> > to add both is it possible to add up just the numbers to the left of the
> > dash? Thanks.

Reply to this Message

Peo Sjoblom - 07 Sep 2007 17:21 GMT

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

Signature

Regards,

Peo Sjoblom

> 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
[quoted text clipped - 27 lines]
>> > the
>> > dash? Thanks.

Reply to this Message

Randi - 07 Sep 2007 17:40 GMT

Thanks. I am a brand new user to Excel - hence the poor layout you've
referred to. Thanks for your halp.

> Do you import these values, if not why are you using what is a really bad
> layout?
[quoted text clipped - 45 lines]
> >> > the
> >> > dash? Thanks.

Reply to this Message

MartinW - 07 Sep 2007 17:30 GMT

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

Reply to this Message

Elkar - 07 Sep 2007 16:54 GMT

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

> I have several columns (formatted as text) containing numbers with
> dashes, i.e.,
[quoted text clipped - 8 lines]
> to add both is it possible to add up just the numbers to the left of the
> dash? Thanks.

Reply to this Message