How do I add a column of cells, but IF a row has (), subtract?
 

I have a column (G) of rows from 1 to 6. I want to add that column,
however, sometimes one of the rows may have a number I need subtracted. How
can I come up with a 'hopefully' short formula to determine IF there is () in
any row of that column, to subtract it. Such as:
row1: 8:00
row2: 6:00
row3: 3:00
row4: (9:00)
row5: 5:00
row6: (7:30)
should equal 5:30
But another time it might be row2 with the () or row 3 and 5.
Or should I use - instead of ()?

How do I add a column of cells, but IF a row has (), subtract?
 

If you use negative amounts, they will sum automatically. They can also be
formatted to show as (amount).

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brandy" wrote in message
...
I have a column (G) of rows from 1 to 6. I want to add that column,
however, sometimes one of the rows may have a number I need subtracted.
How
can I come up with a 'hopefully' short formula to determine IF there is ()
in
any row of that column, to subtract it. Such as:
row1: 8:00
row2: 6:00
row3: 3:00
row4: (9:00)
row5: 5:00
row6: (7:30)
should equal 5:30
But another time it might be row2 with the () or row 3 and 5.
Or should I use - instead of ()?

How do I add a column of cells, but IF a row has (), subtract?
 

Well, that doesn't seem to work for me. It either totall ignores the column
with (3:00) or it gives me an error message if I use -3:00. This particular
column has a beginning total of 21:59, Row 3 is 1:45 (which is added) and row
6 has (3:00) which should be subtracted for a total of 19:44, but the total
shows as 22:44--totally ignoring the (3:00)

"Bob Phillips" wrote:

If you use negative amounts, they will sum automatically. They can also be
formatted to show as (amount).

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brandy" wrote in message
...
I have a column (G) of rows from 1 to 6. I want to add that column,
however, sometimes one of the rows may have a number I need subtracted.
How
can I come up with a 'hopefully' short formula to determine IF there is ()
in
any row of that column, to subtract it. Such as:
row1: 8:00
row2: 6:00
row3: 3:00
row4: (9:00)
row5: 5:00
row6: (7:30)
should equal 5:30
But another time it might be row2 with the () or row 3 and 5.
Or should I use - instead of ()?

How do I add a column of cells, but IF a row has (), subtract?
 

Oops me bad, missed the time bit.

Try this array formula

=SUM(IF(LEFT(A1:A6,1)="(",-MID(A1:A6,2,LEN(A1:A6)-2),A1:A6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

It will fail if negative amounts are greater than non-negative amounts.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brandy" wrote in message
...
Well, that doesn't seem to work for me. It either totall ignores the
column
with (3:00) or it gives me an error message if I use -3:00. This
particular
column has a beginning total of 21:59, Row 3 is 1:45 (which is added) and
row
6 has (3:00) which should be subtracted for a total of 19:44, but the
total
shows as 22:44--totally ignoring the (3:00)

"Bob Phillips" wrote:

If you use negative amounts, they will sum automatically. They can also
be
formatted to show as (amount).

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Brandy" wrote in message
...
I have a column (G) of rows from 1 to 6. I want to add that column,
however, sometimes one of the rows may have a number I need
subtracted.
How
can I come up with a 'hopefully' short formula to determine IF there
is ()
in
any row of that column, to subtract it. Such as:
row1: 8:00
row2: 6:00
row3: 3:00
row4: (9:00)
row5: 5:00
row6: (7:30)
should equal 5:30
But another time it might be row2 with the () or row 3 and 5.
Or should I use - instead of ()?

How do I add a column of cells, but IF a row has (), subtract?
 

By putting the parentheses around the time figure, you will probably be
converting it into a text value, so it will not be considered in the
normal summing. I think the way around this would be to use a helper
column to indicate if the time is to be subtracted or added (you can
use conditional format to turn the cell red if the cell next to it
contains "-" or whatever symbol you choose to indicate subtraction. I
put this data in cells A1 to B6:

0:08:00
0:06:00
0:03:00
0:09:00 -
0:05:00
0:07:30 -

Then in another cell I put this array formula*:

=SUM(IF(B1:B6="-",-A1:A6,A1:A6))

* As this is an array formula, when you have typed it in (or when you
subsequently edit it) you must use CTRL-SHIFT-ENTER instead of ENTER.
If you do this correctly then Excel will wrap curly braces { } around
the formula - you must not type these yourself.

The value I got was 0:05:30, which is correct for this data.

Hope this helps.

Pete