Sumif macro in Excel for SAPBW Bex reports

[Kitte_Cat]

Hi,

I need to add a list of values in a BW SAP BEx report where the number of rows changes evertime the report is run (for a different period).
The values that needs summing, are SHOWN as positives in the report, but the actual value (positive or negative) depends on the cell value in another column. A denotes negative value, B denotes positive value.

Column 1 Report Value Actual Value (not shown in report)
A 15 -15
B 5 5
B 2 2
A 7 -7
B 8 8
A 19 -19
summation 56 -26

How do I go about entering a macro in the spreadsheet where the SUM for the Report Value column will be the absolute value of 26 and which can be used independent of the number of rows in the report?

Any help is very much appreciated!

Thanks

[Paul_Hossler]

I'd use as user defined function (UDF) since IMHO they're more flexible for things like that



Option Explicit

Function AbsoluteSum(PositiveNegativeRange As Range, ValuesRange As Range) As Variant
Dim aryPN As Variant, aryValue As Variant
Dim i As Long

aryPN = PositiveNegativeRange.Columns(1).Value
aryValue = ValuesRange.Columns(1).Value

If UBound(aryPN) <> UBound(aryValue) Then
AbsoluteSum = CVErr(xlErrNum)
Exit Function
End If

For i = LBound(aryPN, 1) To UBound(aryPN, 1)
If UCase(aryPN(i, 1)) = "A" Then
If IsNumeric(aryValue(i, 1)) Then
aryValue(i, 1) = -1# * aryValue(i, 1)
Else
aryValue(i, 1) = 0#
End If

ElseIf UCase(aryPN(i, 1)) <> "B" Then
AbsoluteSum = CVErr(xlErrNA)
Exit Function
End If
Next i
AbsoluteSum = Application.WorksheetFunction.Sum(aryValue)
End Function


Paul