Create Custom Sum Function

I would like to create a custom Sum function using VB.

If would Sum two ranges numerator and denominator and then divide N/D.
It would only add values to the Num and Den if they were positive in both columns eg.

Num Den
5 10 Include
0 5 Don`t Include
10 2 Include
5 -5 Don`t include

So the Num = 15 and Den = 12 so output would be 15/12 = 1.25

So far I have ...
Function Create_aggregate(Numerator As Range, Demoninator As Range)
Dim SumN As Integer
Dim DumD As Integer
For Each i In Numerator
For Each j In Denominator
If i.Value > 0 Then SumN = SumN + i
If j.Value > 0 Then SumD = SumD + j
Else: SumN = SumN - i
Next i
Next j
Create_aggregate = SumN / SumD
End Function

This is effectively the sumif formula which works for one for each but not for two .. Thanks for suggestions

Thanks, but I think the problem is with the two For Each statements. Can you use 2 for each statements?

Hi,
instead of double For Each cycles you have to use something like following:

...
For k = 1 to Numerator.Count 'number of cells in range Numerator
If Numerator(k) > 0 and Denominator(k) > 0 Then
sumN = sumN + Numerator(k)
sumD = sumD + Denominator(k)
End If
Next k
...


Jarek

Hello L2B,

Here is way to simplify your code by letting Excel do the work for you. Just substitute your actual range for "Num" and "Den".

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Leith,
I think L2B wants BOTH of adjacent elements have to be positive.
Another way is using formula, not VBA, for example (Nominator in A1:A4,
Denominator in B1:B4):

=SUMPRODUCT((A1:A4>0)*(B1:B4>0)*(A1:A4))/SUMPRODUCT((A1:A4>0)*(B1:B4>0)*(B1:B4))

Jarek