Better than array formulas which are handed out daily.



--
Regards
Dave Hawley
www.ozgrid.com
"Bob Phillips" wrote in message
...
Still inefficient.

--

HTH

Bob

"ozgrid.com" wrote in message
...
Use Evaluate.


--
Regards
Dave Hawley
www.ozgrid.com
"Bob Phillips" wrote in message
...
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.