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