Sum of absolute values based on criteria

[ckemler]

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

 

[Gary''s Student]

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

 

[Jacob Skaria]

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

 

[Bob Umlas]

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

 

[ckemler]

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

 

[Per Jessen]

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