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


--
Jacob (MVP - Excel)


"Hoss" wrote:

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.