Sum of absolute values based on criteria
 

In this example:
Value Criteria
1 a
-2 a
-3 b
4 b
I want to sum the absolute values of criteria a and another sum of absolute
values of criteria b.
I've been researching this and started learning VBA, but I can't get the
answer. Does anybody have any insight?

Thanks,
Chris

Sum of absolute values based on criteria
 

=SUMPRODUCT(ABS(A1:A4)*(B1:B4="a"))
=SUMPRODUCT(ABS(A1:A4)*(B1:B4="b"))

--
Gary''s Student - gsnu200906

Sum of absolute values based on criteria
 

If you are looking for a formula the below would do.

Please note that this is an array formula. press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"


=SUM(IF(B1:B10="a",ABS(A1:A10)))


If this post helps click Yes
---------------
Jacob Skaria


"ckemler" wrote:

In this example:
Value Criteria
1 a
-2 a
-3 b
4 b
I want to sum the absolute values of criteria a and another sum of absolute
values of criteria b.
I've been researching this and started learning VBA, but I can't get the
answer. Does anybody have any insight?

Thanks,
Chris

Sum of absolute values based on criteria
 

=SUMPRODUCT(N(B2:B5="a"),ABS(A2:A5))
=SUMPRODUCT(N(B2:B5="b"),ABS(A2:A5))

"ckemler" wrote in message
...
In this example:
Value Criteria
1 a
-2 a
-3 b
4 b
I want to sum the absolute values of criteria a and another sum of
absolute
values of criteria b.
I've been researching this and started learning VBA, but I can't get the
answer. Does anybody have any insight?

Thanks,
Chris

Sum of absolute values based on criteria
 

Thanks, This worked with my named ranges whereas the SUMPRODUCT function did
not.

"Jacob Skaria" wrote:

If you are looking for a formula the below would do.

Please note that this is an array formula. press CTRL+SHIFT+ENTER to enter
the formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula}"


=SUM(IF(B1:B10="a",ABS(A1:A10)))


If this post helps click Yes
---------------
Jacob Skaria


"ckemler" wrote:

In this example:
Value Criteria
1 a
-2 a
-3 b
4 b
I want to sum the absolute values of criteria a and another sum of absolute
values of criteria b.
I've been researching this and started learning VBA, but I can't get the
answer. Does anybody have any insight?

Thanks,
Chris

Sum of absolute values based on criteria
 

Hi Chris

You can use the formula below

=SUMPRODUCT(--(B2:B5="A"),ABS(A2:A5))

or you can use this UDF is you prefer VBA:

Public Function AbsSumIF(CriteriaRange As Range, Criteria As String,
SumRange As Range) As Variant
If CriteriaRange.Cells.Count < SumRange.Cells.Count Then
AbsSumIF = CVErr(2023)
Exit Function
End If
For Each cell In CriteriaRange
c = c + 1
If cell.Value = Criteria Then
AbsSumIF = AbsSumIF + Abs(SumRange(c))
End If
Next
End Function

Regards,
Per

"ckemler" skrev i meddelelsen
...
In this example:
Value Criteria
1 a
-2 a
-3 b
4 b
I want to sum the absolute values of criteria a and another sum of
absolute
values of criteria b.
I've been researching this and started learning VBA, but I can't get the
answer. Does anybody have any insight?

Thanks,
Chris