Need Help with "SUM" AND "ABS" in same formula
 

The cells in a SUM formula all include formulas that provide the cells with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need to
have the negative values in the range to stay displayed as negatives and the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!". Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

"Randy" wrote:
Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".

You need to enter =SUM(ABS(range)) as an array formula. That is, press with
ctrl-shift-Enter instead of just Enter. At this point, you can select the
cell, press F2, then press ctrl-shift-Enter.

Alternatively, use the formula =SUMPRODUCT(ABS(range)), which is not an
array formula; simply press Enter.


----- original message -----

"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

Hi Randy,
ISNA OR ISERR will help, could you post your formula. thanks

"Randy" wrote:

The cells in a SUM formula all include formulas that provide the cells with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need to
have the negative values in the range to stay displayed as negatives and the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!". Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

PS....

"Randy" wrote:
Tried using =sum(abs(range)), which works fine if the blank cells are
omitted from the range. If the blank cells are part of the range the
result
is "#VALUE!".

Hmm.... There might be something wrong with my understanding of your
problem. SUM(ABS(range)) should __always__ return #VALUE unless you enter
the formula as an array formula. And if you enter an array formula,
SUM(ABS(range)) should __always__ work regardless of the presence of empty
cells in the range.

But if the "blank cells" contain text, notably "", you will need the
following array formula (again, commit with ctrl-shift-Enter, not just
Enter):

=SUM(IF(range<"",ABS(range)))

Also note: the SUMPRODUCT alternative will not work as a non-array formula.


----- original message -----

"JoeU2004" wrote in message
...
"Randy" wrote:
Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from
the
range. If the blank cells are part of the range the result is "#VALUE!".

You need to enter =SUM(ABS(range)) as an array formula. That is, press
with ctrl-shift-Enter instead of just Enter. At this point, you can
select the cell, press F2, then press ctrl-shift-Enter.

Alternatively, use the formula =SUMPRODUCT(ABS(range)), which is not an
array formula; simply press Enter.


----- original message -----

"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from
the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

Opps, I didn't realize you posted your formula, try

=SUMPRODUCT(ABS(C11:C14))

change c11:c14 for your range

"Eduardo" wrote:

Hi Randy,
ISNA OR ISERR will help, could you post your formula. thanks

"Randy" wrote:

The cells in a SUM formula all include formulas that provide the cells with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need to
have the negative values in the range to stay displayed as negatives and the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!". Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

Try this array formula** :

=SUM(IF(ISNUMBER(A1:A10),ABS(A1:A10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

How about a couple of *non* array formulas:

=SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0")

=SUM(ABS(SUMIF(A1:A10,{"0","<0"})))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

"JoeU200"
Thank You, the =SUM(IF(range<"",ABS(range))) works fine.


"JoeU2004" wrote:

PS....

"Randy" wrote:
Tried using =sum(abs(range)), which works fine if the blank cells are
omitted from the range. If the blank cells are part of the range the
result
is "#VALUE!".

Hmm.... There might be something wrong with my understanding of your
problem. SUM(ABS(range)) should __always__ return #VALUE unless you enter
the formula as an array formula. And if you enter an array formula,
SUM(ABS(range)) should __always__ work regardless of the presence of empty
cells in the range.

But if the "blank cells" contain text, notably "", you will need the
following array formula (again, commit with ctrl-shift-Enter, not just
Enter):

=SUM(IF(range<"",ABS(range)))

Also note: the SUMPRODUCT alternative will not work as a non-array formula.


----- original message -----

"JoeU2004" wrote in message
...
"Randy" wrote:
Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from
the
range. If the blank cells are part of the range the result is "#VALUE!".

You need to enter =SUM(ABS(range)) as an array formula. That is, press
with ctrl-shift-Enter instead of just Enter. At this point, you can
select the cell, press F2, then press ctrl-shift-Enter.

Alternatively, use the formula =SUMPRODUCT(ABS(range)), which is not an
array formula; simply press Enter.


----- original message -----

"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from
the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

"T. Valko"
-Thank You, the formula works fine.

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISNUMBER(A1:A10),ABS(A1:A10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

"RagDyer"
-Thank You, they both work.

"RagDyer" wrote:

How about a couple of *non* array formulas:

=SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0")

=SUM(ABS(SUMIF(A1:A10,{"0","<0"})))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

"RagDyer"
-Thank You, they both work fine.

"RagDyer" wrote:

How about a couple of *non* array formulas:

=SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0")

=SUM(ABS(SUMIF(A1:A10,{"0","<0"})))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values. Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from the
range. If the blank cells are part of the range the result is "#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

You're welcome, and thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Randy" wrote in message
...
"RagDyer"
-Thank You, they both work fine.

"RagDyer" wrote:

How about a couple of *non* array formulas:

=SUMIF(A1:A10,"0")-SUMIF(A1:A10,"<0")

=SUM(ABS(SUMIF(A1:A10,{"0","<0"})))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values.
Need
to
have the negative values in the range to stay displayed as negatives and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from
the
range. If the blank cells are part of the range the result is
"#VALUE!".
Is
there something to add to resolve the error message?

Need Help with "SUM" AND "ABS" in same formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Randy" wrote in message
...
"T. Valko"
-Thank You, the formula works fine.

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(ISNUMBER(A1:A10),ABS(A1:A10)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Randy" wrote in message
...
The cells in a SUM formula all include formulas that provide the cells
with
negative numbers, positive numbers and blank cells. Need to have the
respective values to be used in another formula as absolute values.
Need
to
have the negative values in the range to stay displayed as negatives
and
the
blank cells in the range to stay displayed as blank. Tried using
=sum(abs(range)), which works fine if the blank cells are omitted from
the
range. If the blank cells are part of the range the result is
"#VALUE!".
Is
there something to add to resolve the error message?