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
|