ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Using a UDF within SUMPRODUCT (https://www.excelbanter.com/excel-programming/415113-using-udf-within-sumproduct.html)

Gary''s Student
Using a UDF within SUMPRODUCT
 
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 - gsnu200797

Ker_01
Using a UDF within SUMPRODUCT
 
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

"Gary''s Student" wrote in message
...
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 - gsnu200797



Ker_01
Using a UDF within SUMPRODUCT
 
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


"Gary''s Student" wrote in message
...
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 - gsnu200797



Mike H
Using a UDF within SUMPRODUCT
 
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

"Gary''s Student" wrote:

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 - gsnu200797

Charles Williams
Using a UDF within SUMPRODUCT
 
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

"Gary''s Student" wrote in message
...
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 - gsnu200797



Gary''s Student
Using a UDF within SUMPRODUCT
 
Thank you.
--
Gary''s Student - gsnu200797


"ker_01" wrote:

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


"Gary''s Student" wrote in message
...
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 - gsnu200797




Lars-Åke Aspelin[_2_]
Using a UDF within SUMPRODUCT
 
On Mon, 4 Aug 2008 10:40:14 -0700, Gary''s Student
wrote:

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.


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

Gary''s Student
Using a UDF within SUMPRODUCT
 
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''s Student - gsnu2007xx


"Charles Williams" wrote:

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

"Gary''s Student" wrote in message
...
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 - gsnu200797




Gary''s Student
Using a UDF within SUMPRODUCT
 
Thanks!!

Your method works very well.
--
Gary''s Student - gsnu2007xx


"Lars-Ã…ke Aspelin" wrote:

On Mon, 4 Aug 2008 10:40:14 -0700, Gary''s Student
wrote:

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.


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

Bob Phillips[_3_]
Using a UDF within SUMPRODUCT
 
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

--
__________________________________
HTH

Bob

"Gary''s Student" wrote in message
...
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''s Student - gsnu2007xx


"Charles Williams" wrote:

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

"Gary''s Student" wrote in
message
...
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 - gsnu200797

Gary''s Student
Using a UDF within SUMPRODUCT
 
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.
--
Gary''s Student - gsnu200797


"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Gary''s Student" wrote in message
...
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''s Student - gsnu2007xx


"Charles Williams" wrote:

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

"Gary''s Student" wrote in
message
...
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 - gsnu200797


All times are GMT +1. The time now is 05:40 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com