J
JE McGimpsey
One way:
=SUMPRODUCT(--(A1:A100="criterion"),ABS(B1:B100))
=SUMPRODUCT(--(A1:A100="criterion"),ABS(B1:B100))
H
HGood
Can I take a range like the following:
-200
300
-50
and sum the absolute value of each of these, so that the Total will be 550?
I'm using the Sumif function and would like to apply the ABS to every value
in this range.
Thanks,
Harold
-200
300
-50
and sum the absolute value of each of these, so that the Total will be 550?
I'm using the Sumif function and would like to apply the ABS to every value
in this range.
Thanks,
Harold
C
crispbd
One way to simplify the task would be to fill an adjacent column of the
sum range with the ABS equivalent (using abs(cell)), then use the new
column as your sum range in the SUMIF function.
sum range with the ABS equivalent (using abs(cell)), then use the new
column as your sum range in the SUMIF function.
D
Don Guillett
one way
=SUM(ABS(D1
22))+SUMIF(D122,">0")
=SUM(ABS(D1
22))+SUMIF(D122,">0")
D
Don Guillett
Good. But if nothing in col A then try just using blank. Any blank column
will do.
=SUMPRODUCT(--(A1:A100=""),ABS(B1:B100))
will do.
=SUMPRODUCT(--(A1:A100=""),ABS(B1:B100))
J
JE McGimpsey
Not sure I follow...
If there is nothing in column A, one could instead use
=SUMPRODUCT(ABS(B1:B100))
or, array-entered,
=SUM(ABS(B1:B100))
Since the OP said he was using SUMIF() I assumed there was another
criterion.
If there is nothing in column A, one could instead use
=SUMPRODUCT(ABS(B1:B100))
or, array-entered,
=SUM(ABS(B1:B100))
Since the OP said he was using SUMIF() I assumed there was another
criterion.
D
Don Guillett
Right your are JE
H
HGood
Thanks, this did the trick!
Harold
========
Harold
========
JE McGimpsey said:
D
Dana DeLouis
If you wish to use SumIf, another option might be something like this. This
behaves a little nicer with any text or errors within the range.
=SUMIF(A1:A10,">0")-SUMIF(A1:A10,"<0")
HTH
behaves a little nicer with any text or errors within the range.
=SUMIF(A1:A10,">0")-SUMIF(A1:A10,"<0")
HTH