Using a UDF within SUMPRODUCT

  • Thread starter Thread starter Gary''s Student
  • Start date Start date
G

Gary''s Student

I am trying to use a UDF within a SUMPRODUCT formula, but am having a problem
getting the UDF to correctly return an array. Simplified data in A1 thru B24:

23 1
14 2
16 2
9 1
21 2
10 2
20 1
17 3
13 1
15 1
22 3
8 3
2 1
19 1
3 1
1 2
4 2
11 3
6 2
18 2
5 3
7 2
12 1
24 3

I need to count the number of rows in which the value in column A is prime
and the value in column B is 3. The values in column A are always 25 or
less. My sad attempt at a UDF is:

Function prime(r As Range) As Variant
pm = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)
Dim v() As Variant
ReDim v(1 To r.Count)
j = 1
For Each rr In r
x = rr.Value
v(j) = 0
For i = 0 To 9
If x = pm(i) Then
v(j) = 1
End If
Next
j = j + 1
Next
prime = v
End Function

The function should return a 1 if the argument is prime, otherwise 0.

The function works for single items like:
=prime(A1)
The function also works within SUMPRODUCT like:
=SUMPRODUCT(--(prime(A1:A24)=1))

The function, however, returns #VALUE!
for:
=SUMPRODUCT(--(prime(A1:A24)=1),--(B1:B24=3))

(I usually get this if the sumproduct inputs are of different lengths)

This is not urgent since I can use a helper column until I can get
sumproduct to work.

Thanks in advance for any help.
 
Gary's student-

I tried as well using the following (less complex for my brain) function.
I'm not guru, but my best guess is that initially you are feeding in a range
of one cell, so it can handle it accordingly. When you pass it a larger
range in the sumproduct formula, your prime formula is taking the whole
range at once instead of each cell one at a time. I haven't found it yet,
but I wonder if there is a way to make your prime function's parameter a
range of a single cell size only?

'my shortened function below

Function prime(r As Range) As Variant
primenums = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)

If IsError(Application.Match(r.Value, primenums, False)) Then
'no match
prime = False
Else
'matches a number in the primenums array
prime = True

End If

End Function
 
Here is a function that takes into account a multiple cell range, and builds
the sumproduct into the function to avoid the issue I mentioned in my
previous response:

Function prime(r As Range, t As Range) As Variant

TotalPrimeCount = 0
primenums = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)

Dim IntermediateArray()
NumRangeCells = r.Cells.Count
ReDim IntermediateArray(1 To NumRangeCells)

For x = 1 To NumRangeCells

'Check Prime status
If IsError(Application.Match(r(x), primenums, False)) Then
'no match
prime = False
Else
'matches a number in the primenums array
prime = True
End If

'Check for "3"
If t(x) = 3 Then
Check3 = True
Else
Check3 = False
End If

If prime = True And Check3 = True Then TotalPrimeCount = TotalPrimeCount + 1

Next

prime = TotalPrimeCount

End Function
 
Hi,

I don't know the answer to your question but in the meantime you shouldn't
be including 1 in your array of prime numbers. 1 is unity not prime.

Mike
 
You need to make v 2 dimensional (ranges are always 2D)
something like this

Option Base 1
Function prime(r As Range) As Variant
pm = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)
Dim v() As Variant
ReDim v(1 To r.Count, 1)
j = 1
For Each rr In r
x = rr.Value
v(j, 1) = 0
For i = 1 To 10
If x = pm(i) Then
v(j, 1) = 1
End If
Next
j = j + 1
Next
prime = v
End Function

regards
Charles
 
Thank you.
--
Gary''s Student - gsnu200797


ker_01 said:
Here is a function that takes into account a multiple cell range, and builds
the sumproduct into the function to avoid the issue I mentioned in my
previous response:

Function prime(r As Range, t As Range) As Variant

TotalPrimeCount = 0
primenums = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)

Dim IntermediateArray()
NumRangeCells = r.Cells.Count
ReDim IntermediateArray(1 To NumRangeCells)

For x = 1 To NumRangeCells

'Check Prime status
If IsError(Application.Match(r(x), primenums, False)) Then
'no match
prime = False
Else
'matches a number in the primenums array
prime = True
End If

'Check for "3"
If t(x) = 3 Then
Check3 = True
Else
Check3 = False
End If

If prime = True And Check3 = True Then TotalPrimeCount = TotalPrimeCount + 1

Next

prime = TotalPrimeCount

End Function
Click to expand...
 
I am trying to use a UDF within a SUMPRODUCT formula, but am having a problem
getting the UDF to correctly return an array. Simplified data in A1 thru B24:

23 1
14 2
16 2
9 1
21 2
10 2
20 1
17 3
13 1
15 1
22 3
8 3
2 1
19 1
3 1
1 2
4 2
11 3
6 2
18 2
5 3
7 2
12 1
24 3

I need to count the number of rows in which the value in column A is prime
and the value in column B is 3. The values in column A are always 25 or
less. My sad attempt at a UDF is:

Function prime(r As Range) As Variant
pm = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)
Dim v() As Variant
ReDim v(1 To r.Count)
j = 1
For Each rr In r
x = rr.Value
v(j) = 0
For i = 0 To 9
If x = pm(i) Then
v(j) = 1
End If
Next
j = j + 1
Next
prime = v
End Function

The function should return a 1 if the argument is prime, otherwise 0.

The function works for single items like:
=prime(A1)
The function also works within SUMPRODUCT like:
=SUMPRODUCT(--(prime(A1:A24)=1))

The function, however, returns #VALUE!
for:
=SUMPRODUCT(--(prime(A1:A24)=1),--(B1:B24=3))

(I usually get this if the sumproduct inputs are of different lengths)

This is not urgent since I can use a helper column until I can get
sumproduct to work.

Thanks in advance for any help.
Click to expand...


Add this to your UDF to make the result from prime being an array in
"the right direction".

prime = WorksheetFunction.Transpose(v)

Alternatively you can choose to use the TRANSPOSE function to one of
the parameters of SUMPRODUCT, like

=SUMPRODUCT(- -(TRANSPOSE(prime(A1:A24)=1)),- -(B1:B24=3))

or

=SUMPRODUCT(- -(prime(A1:A24)=1),- -TRANSPOSE((B1:B24=3)))

Hope this helps / Lars-Åke
 
Thanks you Charles.

I did not realize that the UDF needs to return a 2-D array for SUMPRODUCT
compatibility.

Your modification works just fine!!
 
Gary,

Charles makes the UDF work, but there is still a bug in it, and an
unnecessary lbound of the 2nd dimension.

This addresses both points

Function prime(r As Range) As Variant
pm = Array(1, 2, 3, 5, 7, 11, 13, 17, 19, 23)
Dim v() As Variant
ReDim v(1 To r.Count, 1 To 1)
j = 1
For Each rr In r
x = rr.Value
v(j, 1) = 0
For i = 1 To 10
If x = pm(i - 1) Then
v(j, 1) = 1
End If
Next
j = j + 1
Next
prime = v
End Function

Also, as the array is returning 0 and 1 already, you don't need

=SUMPRODUCT(--(prime(A1:A24)=1))

you can just use

=SUMPRODUCT((prime(A1:A24)=1))

and finally, here is a prime routine you can use, and a mod to your routine
to use it

Function prime(r As Range) As Variant
Dim v() As Variant
ReDim v(1 To r.Count, 1 To 1)
j = 1
For Each rr In r
v(j, 1) = -CLng(IsPrime(rr.Value))
j = j + 1
Next
prime = v
End Function

'-----------------------------------------------------------­------
Function IsPrime(num As Long) As Boolean
'-----------------------------------------------------------­------
Dim i As Long
IsPrime = True

If num = 2 Then
IsPrime = True
ElseIf num Mod 2 = 0 Then
IsPrime = False
Else
For i = 3 To num ^ 0.5 Step 2
If num Mod i = 0 Then
IsPrime = False
End If
Next i
End If
End Function
 
Thanks for the help & advise. Getting UDF's to return arrays has been a
problem for me. I think I am beginning to see the issues involved.
 
Back
Top