Sum of absolute values based on criteria

  • Thread starter Thread starter ckemler
  • Start date Start date
C

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
 
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
 
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
 
Back
Top