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

--

HTH

Bob

"Hoss" wrote in message
...
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.