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

[Guest]

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 ()?

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Pete_UK]

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