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

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

--
Gary''s Student - gsnu200906

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

=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

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

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