sumproduct in UDF
 

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.

sumproduct in UDF
 

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.

sumproduct in UDF
 

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.

sumproduct in UDF
 

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.

sumproduct in UDF
 

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.

sumproduct in UDF
 

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.

sumproduct in UDF
 

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

--

HTH

Bob

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

sumproduct in UDF
 

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

"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.