Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SumProduct | Excel Worksheet Functions | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
help with sumproduct | Excel Discussion (Misc queries) |