Subtotal of Abs of a range?
 

Greetings! Am stumped. Need a sum of the absolute value of row-filtered
elements in a column. SUBTOTAL does this admirably, but it appears that
SUBTOTAL does not accommodate anything but a simple range as it target, in
the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand, allows
for a function such as ABS to be embedded as its argument, but does not
screen out filtered values.

A work-around is to create an intermediate column that ABS's the original
column and then apply SUBTOTAL to it. However, in the interest of
compactness, the question becomes: is there a way to cause SUM to skip
filtered-out elements, or a way to create the equivalent of
SUBTOTAL(9,ABS(Range)).

Thanks!
George

Subtotal of Abs of a range?
 

Incidentally, SUM as imagined here would be expressed as {SUM(ABS(Range))},
an array formula, that in this form doesn't skip filtered-out rows. It
appears that SUBTOTAL does not support array usage (or is it vice versa?).


"G Lykos" wrote in message
...
Greetings! Am stumped. Need a sum of the absolute value of row-filtered
elements in a column. SUBTOTAL does this admirably, but it appears that
SUBTOTAL does not accommodate anything but a simple range as it target, in
the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand,
allows
for a function such as ABS to be embedded as its argument, but does not
screen out filtered values.

A work-around is to create an intermediate column that ABS's the original
column and then apply SUBTOTAL to it. However, in the interest of
compactness, the question becomes: is there a way to cause SUM to skip
filtered-out elements, or a way to create the equivalent of
SUBTOTAL(9,ABS(Range)).

Thanks!
George

Subtotal of Abs of a range?
 

{=SUM(ABS(Range))}, to be more exact ...


"G Lykos" wrote in message
...
Incidentally, SUM as imagined here would be expressed as
{SUM(ABS(Range))},
an array formula, that in this form doesn't skip filtered-out rows. It
appears that SUBTOTAL does not support array usage (or is it vice versa?).


"G Lykos" wrote in message
...
Greetings! Am stumped. Need a sum of the absolute value of
row-filtered
elements in a column. SUBTOTAL does this admirably, but it appears that
SUBTOTAL does not accommodate anything but a simple range as it target,
in
the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand,
allows
for a function such as ABS to be embedded as its argument, but does not
screen out filtered values.

A work-around is to create an intermediate column that ABS's the
original
column and then apply SUBTOTAL to it. However, in the interest of
compactness, the question becomes: is there a way to cause SUM to skip
filtered-out elements, or a way to create the equivalent of
SUBTOTAL(9,ABS(Range)).

Thanks!
George

Subtotal of Abs of a range?
 

Try this:

The range to sum is B2:B10 (filtered or unfiltered):

Array entered:

=SUM(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-MIN(ROW(B2:B10)),,1)),ABS(B2:B10)))

Biff

"G Lykos" wrote in message
...
Greetings! Am stumped. Need a sum of the absolute value of row-filtered
elements in a column. SUBTOTAL does this admirably, but it appears that
SUBTOTAL does not accommodate anything but a simple range as it target, in
the pseudo-syntax SUBTOTAL(Function,Range). SUM, on the other hand,
allows
for a function such as ABS to be embedded as its argument, but does not
screen out filtered values.

A work-around is to create an intermediate column that ABS's the original
column and then apply SUBTOTAL to it. However, in the interest of
compactness, the question becomes: is there a way to cause SUM to skip
filtered-out elements, or a way to create the equivalent of
SUBTOTAL(9,ABS(Range)).

Thanks!
George