Function mySP(positive As Boolean, dec As Long, ParamArray rng())
Dim sConditions As String
Dim sRanges As String
Dim i As Long

sConditions = "--(" & rng(0).Address(, , , True)
sRanges = "),ROUND(" & rng(0).Address(, , , True)
For i = LBound(rng) + 1 To UBound(rng)
sConditions = sConditions & "*" & rng(i).Address(, , , True)
sRanges = sRanges & "*" & rng(i).Address(, , , True)
Next i
mySP = Evaluate("=SumProduct(" & sConditions & sRanges & _
IIf(positive, "0", "<0") & "," & dec & "))")
End Function

call like

=mysp(FALSE,2,L1:L3,M1:M3,N1:N3)

for negatives

=mysp(TRUE,2,L1:L3,M1:M3,N1:N3)

for positives

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"dribler2" wrote in message
...
i am amazed with the huge help of the UDF below,
yet i am still lacking some very minor touch of possible calculating
conditions.
like,
I need to do something like this
=mySP(+,2, A1:A100,B1:B100,C1:K100) only to provide the sum of positive
rounded products
or
=mySP(-,2, A1:A100,B1:B100,C1:K100) only to provide the sum of negative
rounded products

this may be magic but i hope excel VBA is possible.

Below is the checked result from my previous post
'Use like =mySP(2, A1:A100,B1:B100,C1:K100)

Function mySPR(dec As Long, ParamArray rng())
Dim sRanges As String
Dim i As Long

For i = LBound(rng) To UBound(rng)
sRanges = sRanges & rng(i).Address(, , , True) & "*"
Next i
mySPR = Evaluate("=SumProduct(Round(" & _
Left(sRanges, Len(sRanges) - 1) & "," & dec & "))")
End Function

TFH "thanks for help" happy holidays
dribler2