sumproduct in UDF

[Hoss]

Hello all,
Thanks for your help, I am stumped. I am using excel 2003 so cannot use the
countifs. I have been pouring through the discussion site to try to solve my
problem. I am trying to create a UDF that can count the number of instances
when two criteria are met. To accomplish this I am trying to use the
sumproduct function with the dashes and am aware of the difficulties that it
creates. For example, there is a data set where a salesperson has several
transactions and each is rated a score 1 through 4 on a number of questions.
I want the UDF to return the number of times each salesperson received a
score of 4. The salespersons ratings are in various columns which is why I
am trying to get the inputs to determine the range used in the formula. I
need to compile all the results for each salesperson. I am trying to use a
range I have previously created for a different purpose because I am trying
to reduce the number of defined ranges. I am still somewhat of a novice with
vb. Here is what I have right now which returns a #Value!.

Function CountTableIf(SumTable As Range, Question1 As String, Criteria1 As
String, Question2 As String, Criteria2 As Integer)

Application.Volatile
Dim sformula As String
Dim Table2 As Range
Set Table2 = Table.Columns(Application.Match(Question1, SumTable.Rows(1), 0))
Dim Table3 As Range
Set Table3 = Table.Columns(Application.Match(Question2, SumTable.Rows(1), 0))

sformula = "SumProduct(--(" & Table2 & "=" & Criteria1 & "),--(" &
Table3 & "=" & Criteria2 & "))"

CountTableIf = Evaluate(sformula)

End Function

Thanks for your help.

 

[Jacob Skaria]

Hi "Hoss"

I have not tried to re-write the UDF instead made few corrections...


Function CountTableIf(SumTable As Range, Question1 As String, Criteria1 As _
String, Question2 As String, Criteria2 As Integer)

Application.Volatile
Dim sformula As String
Dim Table2 As Range
Dim Table3 As Range

Set Table2 = SumTable.Columns(Application.Match(Question1, _
SumTable.Rows(1), 0))
Set Table3 = SumTable.Columns(Application.Match(Question2, _
SumTable.Rows(1), 0))

sformula = "SumProduct(--(" & Table2.Address & "=" & Criteria1 & "),--(" & _
Table3.Address & "=" & Criteria2 & "))"

CountTableIf = Evaluate(sformula)

End Function

 

[Bob Phillips]

Why would you use SUMPRODUCT in a UDF, it will be even less efficient than
SP in a worksheet.

 

[ozgrid.com]

Use Evaluate.

 

[Bob Phillips]

Still inefficient.

--

HTH

Bob

Use Evaluate.

 

[ozgrid.com]

Better than array formulas which are handed out daily.

 

[Bob Phillips]

Disagree, you are still using the complex formula, just adding another level
of interface, so how is it better?

 

[Hoss]

Thanks all for your comments. I am using the Sumproduct because it is the
only why I can think of to accomplish my goals. I have chosen to create a
UDF with sumproduct because it will allow the formula to be somewhat dynamic
with changes to the data set (questions added or deleted changing the
columns). The other options I could imagine included having to set up
complex formulas for 5 people for 5 questions for 12 months. Thank you Jacob
for your corrections to my code, I appreciate it. If anyone has a better,
more efficient way of getting what I need with or without a UDF I am willing
to give it a try.

Thanks, Hoss