SumProduct & Match in a UDF

I have a w-sheet of queried data that will max at about 60 columns and
27,000 rows. Am experimenting w/ SumProduct formulas as a means to sum data
based on multiple criteria, but am having trouble translating them into VBA.
(Want to go VBA route to simplify for users). My questions:
1) Have read in the postings that SumProduct may be a memory hog, end up
being slow for users. Is that true?? If yes, blow the below questions off,
and kindly suggest a different VBA solution than below, pls. If the answer
is no, this is what Im up against:
2) Heres an example of a worksheet formula Im currently (successfully)
using, but am having trouble getting into VBA
=SUMPRODUCT(--(ISNUMBER(MATCH(Org,zh6A,0))),--(ISNUMBER(MATCH(Acct,SelectedAccts,0))),Actual12)
Org = named range of approx 27,000 rows, 1 column on the main data sheet
talked about above. Data can be either numeric or string, includes
duplicates.
zh6A = named range of approx 200 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Acct = another named range of approx 27,000 rows, 1 column on the main data
sheet. Data can be either numeric or string, includes duplicates.
SelectedAccts = named range of approx 50 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Actual12 = another named range of approx 27,000 rows, 1 column on the main
data sheet. Data is numeric, no dups.
3) Heres the UDF Ive come up with so far (returns the #Value error):
Function ARES(ROrgs, RAccts, DataType_Mo)
Dim Org
Org = Worksheets("Data").Range("Org")
Dim Acct
Acct = Worksheets("Data").Range("Acct")
ARES = Evaluate( "SumProduct(--(IsNumber(Match(" & Org & "," & ROrgs & _
",0))),--(IsNumber(Match(" & Acct & "," & RAccts & ",0)))," & DataType_Mo &
")€�)

My plan is for the users to be able to provide named ranges of data for the
UDF arguments (e.g., the zh6A named range for the ROrgs argument), and then
the Org and Acct variables are named ranges that will already be defined in
the w-book and always used in the UDF. Thanks much for anyones help.

--
BillW

SUMPRODUCT, like array formulae, can be quite resource hungry when you have
large ranges of data, and multiple instances of the formula.

However, embedding them as evaluated statements in a UDF is adding two extra
layers of work, which can only slow them down even more.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"BillW" wrote in message
...
SumProduct & Match in a UDF

I have a w-sheet of queried data that will max at about 60 columns and
27,000 rows. Am experimenting w/ SumProduct formulas as a means to sum
data
based on multiple criteria, but am having trouble translating them into
VBA.
(Want to go VBA route to simplify for users). My questions:
1) Have read in the postings that SumProduct may be a memory hog, end up
being slow for users. Is that true?? If yes, blow the below questions
off,
and kindly suggest a different VBA solution than below, pls. If the
answer
is no, this is what I'm up against:
2) Here's an example of a worksheet formula I'm currently (successfully)
using, but am having trouble getting into VBA:
=SUMPRODUCT(--(ISNUMBER(MATCH(Org,zh6A,0))),--(ISNUMBER(MATCH(Acct,SelectedAccts,0))),Actual12)
Org = named range of approx 27,000 rows, 1 column on the main data sheet
talked about above. Data can be either numeric or string, includes
duplicates.
zh6A = named range of approx 200 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Acct = another named range of approx 27,000 rows, 1 column on the main
data
sheet. Data can be either numeric or string, includes duplicates.
SelectedAccts = named range of approx 50 rows, 1 column on a separate
sheet.
Numeric or string data, no duplicates.
Actual12 = another named range of approx 27,000 rows, 1 column on the main
data sheet. Data is numeric, no dups.
3) Here's the UDF I've come up with so far (returns the #Value error):
Function ARES(ROrgs, RAccts, DataType_Mo)
Dim Org
Org = Worksheets("Data").Range("Org")
Dim Acct
Acct = Worksheets("Data").Range("Acct")
ARES = Evaluate( "SumProduct(--(IsNumber(Match(" & Org & "," & ROrgs &
_
",0))),--(IsNumber(Match(" & Acct & "," & RAccts & ",0)))," & DataType_Mo
&
")")

My plan is for the users to be able to provide named ranges of data for
the
UDF arguments (e.g., the zh6A named range for the ROrgs argument), and
then
the Org and Acct variables are named ranges that will already be defined
in
the w-book and always used in the UDF. Thanks much for anyone's help.

--
BillW

And your UDF wont work properly if you refer to ranges which are not in the
UDF parameter list.

A UDF is usually faster than SUMPRODUCT/Array formulae if you get all the
data from ranges into variant arrays using
vArr=Rng
and then process the data in the arrays.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Bob Phillips" wrote in message
...
SUMPRODUCT, like array formulae, can be quite resource hungry when you
have large ranges of data, and multiple instances of the formula.

However, embedding them as evaluated statements in a UDF is adding two
extra layers of work, which can only slow them down even more.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"BillW" wrote in message
...
SumProduct & Match in a UDF

I have a w-sheet of queried data that will max at about 60 columns and
27,000 rows. Am experimenting w/ SumProduct formulas as a means to sum
data
based on multiple criteria, but am having trouble translating them into
VBA.
(Want to go VBA route to simplify for users). My questions:
1) Have read in the postings that SumProduct may be a memory hog, end up
being slow for users. Is that true?? If yes, blow the below questions
off,
and kindly suggest a different VBA solution than below, pls. If the
answer
is no, this is what I'm up against:
2) Here's an example of a worksheet formula I'm currently (successfully)
using, but am having trouble getting into VBA:
=SUMPRODUCT(--(ISNUMBER(MATCH(Org,zh6A,0))),--(ISNUMBER(MATCH(Acct,SelectedAccts,0))),Actual12)
Org = named range of approx 27,000 rows, 1 column on the main data sheet
talked about above. Data can be either numeric or string, includes
duplicates.
zh6A = named range of approx 200 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Acct = another named range of approx 27,000 rows, 1 column on the main
data
sheet. Data can be either numeric or string, includes duplicates.
SelectedAccts = named range of approx 50 rows, 1 column on a separate
sheet.
Numeric or string data, no duplicates.
Actual12 = another named range of approx 27,000 rows, 1 column on the
main
data sheet. Data is numeric, no dups.
3) Here's the UDF I've come up with so far (returns the #Value error):
Function ARES(ROrgs, RAccts, DataType_Mo)
Dim Org
Org = Worksheets("Data").Range("Org")
Dim Acct
Acct = Worksheets("Data").Range("Acct")
ARES = Evaluate( "SumProduct(--(IsNumber(Match(" & Org & "," & ROrgs &
_
",0))),--(IsNumber(Match(" & Acct & "," & RAccts & ",0)))," & DataType_Mo
&
")")

My plan is for the users to be able to provide named ranges of data for
the
UDF arguments (e.g., the zh6A named range for the ROrgs argument), and
then
the Org and Acct variables are named ranges that will already be defined
in
the w-book and always used in the UDF. Thanks much for anyone's help.

--
BillW

Thanks for your input, appreciate it. Was glad to find SumProduct as
potential answer, but need to keep things skinny cuz of potential size of
data.
I definitely need help w/ arrays & VBA, just learning. How do I accomplish
the below?
Main Datasheet consists of
A B C D
1 10000 3000 10 20
2 10000 9EXP 20 30
3 12510A 10000 30 40
4 12510 3000 40 50
A1:A4 is named range Org. B1:B4 = named range Acct. C1:C4 = named range
Actl11 and D1:D4 = named range Bgt11, both columns with data to be summed.
All these named ranges will always be available for formula, VBA purposes.
Users via dialog boxes will (a) provide a named range called ROrgs with
comparison criteria to match in Org, (b) provide a named range called RAccts
with comparison criteria to match in Acct, and (c) designate which column,
Actl11 or Bgt11, to sum records for. So say ROrgs has data of 10000 and
12510A in it, RAccts has 3000 and 10000 in it, and they want Bgt11 summed.
Answer is 60, but not sure how to get there from here using arrays in VBA.
(This is much-simplified example, actually have 5 different criteria columns
so far, with the different combinations increasing the decision points
exponentially, but will nest things if you can get me started here.)
Thanks again for all help.

--
BillW


"Charles Williams" wrote:

And your UDF wont work properly if you refer to ranges which are not in the
UDF parameter list.

A UDF is usually faster than SUMPRODUCT/Array formulae if you get all the
data from ranges into variant arrays using
vArr=Rng
and then process the data in the arrays.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Bob Phillips" wrote in message
...
SUMPRODUCT, like array formulae, can be quite resource hungry when you
have large ranges of data, and multiple instances of the formula.

However, embedding them as evaluated statements in a UDF is adding two
extra layers of work, which can only slow them down even more.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"BillW" wrote in message
...
SumProduct & Match in a UDF

I have a w-sheet of queried data that will max at about 60 columns and
27,000 rows. Am experimenting w/ SumProduct formulas as a means to sum
data
based on multiple criteria, but am having trouble translating them into
VBA.
(Want to go VBA route to simplify for users). My questions:
1) Have read in the postings that SumProduct may be a memory hog, end up
being slow for users. Is that true?? If yes, blow the below questions
off,
and kindly suggest a different VBA solution than below, pls. If the
answer
is no, this is what I'm up against:
2) Here's an example of a worksheet formula I'm currently (successfully)
using, but am having trouble getting into VBA:
=SUMPRODUCT(--(ISNUMBER(MATCH(Org,zh6A,0))),--(ISNUMBER(MATCH(Acct,SelectedAccts,0))),Actual12)
Org = named range of approx 27,000 rows, 1 column on the main data sheet
talked about above. Data can be either numeric or string, includes
duplicates.
zh6A = named range of approx 200 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Acct = another named range of approx 27,000 rows, 1 column on the main
data
sheet. Data can be either numeric or string, includes duplicates.
SelectedAccts = named range of approx 50 rows, 1 column on a separate
sheet.
Numeric or string data, no duplicates.
Actual12 = another named range of approx 27,000 rows, 1 column on the
main
data sheet. Data is numeric, no dups.
3) Here's the UDF I've come up with so far (returns the #Value error):
Function ARES(ROrgs, RAccts, DataType_Mo)
Dim Org
Org = Worksheets("Data").Range("Org")
Dim Acct
Acct = Worksheets("Data").Range("Acct")
ARES = Evaluate( "SumProduct(--(IsNumber(Match(" & Org & "," & ROrgs &
_
",0))),--(IsNumber(Match(" & Acct & "," & RAccts & ",0)))," & DataType_Mo
&
")")

My plan is for the users to be able to provide named ranges of data for
the
UDF arguments (e.g., the zh6A named range for the ROrgs argument), and
then
the Org and Acct variables are named ranges that will already be defined
in
the w-book and always used in the UDF. Thanks much for anyone's help.

--
BillW

Sounds like you should be using a proper database rather than Excel (or
maybe SUMIFS in Excel 2007)

anyway here is some code to get you started

public function Bill(theData as range, OrgList as range, AcctList as range,
....
dim vData as variant
dim j as long
dim k as long
dim oOrg as range
dim oAcct as range

vData=theData
'
' vdata now contains a 2-dimensional 1-based array of the data from theRange
'
for j=1 to ubound(vData,1)
for each oOrg in OrgList
if oorg.value=vdata(j,1) then
for each oAcct.value in AcctList
if oAcct.value=vdata(j,2) then Bill=Bill+vdata(j,4)
next oAcct
endif
next oOrg
next j
end function

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"BillW" wrote in message
...
Thanks for your input, appreciate it. Was glad to find SumProduct as
potential answer, but need to keep things skinny cuz of potential size of
data.
I definitely need help w/ arrays & VBA, just learning. How do I
accomplish
the below?
Main Datasheet consists of
A B C D
1 10000 3000 10 20
2 10000 9EXP 20 30
3 12510A 10000 30 40
4 12510 3000 40 50
A1:A4 is named range Org. B1:B4 = named range Acct. C1:C4 = named range
Actl11 and D1:D4 = named range Bgt11, both columns with data to be summed.
All these named ranges will always be available for formula, VBA purposes.
Users via dialog boxes will (a) provide a named range called ROrgs with
comparison criteria to match in Org, (b) provide a named range called
RAccts
with comparison criteria to match in Acct, and (c) designate which column,
Actl11 or Bgt11, to sum records for. So say ROrgs has data of 10000 and
12510A in it, RAccts has 3000 and 10000 in it, and they want Bgt11 summed.
Answer is 60, but not sure how to get there from here using arrays in VBA.
(This is much-simplified example, actually have 5 different criteria
columns
so far, with the different combinations increasing the decision points
exponentially, but will nest things if you can get me started here.)
Thanks again for all help.

--
BillW


"Charles Williams" wrote:

And your UDF wont work properly if you refer to ranges which are not in
the
UDF parameter list.

A UDF is usually faster than SUMPRODUCT/Array formulae if you get all the
data from ranges into variant arrays using
vArr=Rng
and then process the data in the arrays.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Bob Phillips" wrote in message
...
SUMPRODUCT, like array formulae, can be quite resource hungry when you
have large ranges of data, and multiple instances of the formula.

However, embedding them as evaluated statements in a UDF is adding two
extra layers of work, which can only slow them down even more.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"BillW" wrote in message
...
SumProduct & Match in a UDF

I have a w-sheet of queried data that will max at about 60 columns and
27,000 rows. Am experimenting w/ SumProduct formulas as a means to
sum
data
based on multiple criteria, but am having trouble translating them
into
VBA.
(Want to go VBA route to simplify for users). My questions:
1) Have read in the postings that SumProduct may be a memory hog, end
up
being slow for users. Is that true?? If yes, blow the below questions
off,
and kindly suggest a different VBA solution than below, pls. If the
answer
is no, this is what I'm up against:
2) Here's an example of a worksheet formula I'm currently
(successfully)
using, but am having trouble getting into VBA:
=SUMPRODUCT(--(ISNUMBER(MATCH(Org,zh6A,0))),--(ISNUMBER(MATCH(Acct,SelectedAccts,0))),Actual12)
Org = named range of approx 27,000 rows, 1 column on the main data
sheet
talked about above. Data can be either numeric or string, includes
duplicates.
zh6A = named range of approx 200 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Acct = another named range of approx 27,000 rows, 1 column on the main
data
sheet. Data can be either numeric or string, includes duplicates.
SelectedAccts = named range of approx 50 rows, 1 column on a separate
sheet.
Numeric or string data, no duplicates.
Actual12 = another named range of approx 27,000 rows, 1 column on the
main
data sheet. Data is numeric, no dups.
3) Here's the UDF I've come up with so far (returns the #Value error):
Function ARES(ROrgs, RAccts, DataType_Mo)
Dim Org
Org = Worksheets("Data").Range("Org")
Dim Acct
Acct = Worksheets("Data").Range("Acct")
ARES = Evaluate( "SumProduct(--(IsNumber(Match(" & Org & "," &
ROrgs &
_
",0))),--(IsNumber(Match(" & Acct & "," & RAccts & ",0)))," &
DataType_Mo
&
")")

My plan is for the users to be able to provide named ranges of data
for
the
UDF arguments (e.g., the zh6A named range for the ROrgs argument), and
then
the Org and Acct variables are named ranges that will already be
defined
in
the w-book and always used in the UDF. Thanks much for anyone's help.

--
BillW

Works like a charm, thank you!!!
And agreed the database tool. Have been working in Crystal for a long
time, but users always complain it's too hard, so am trying to squeeze a more
user-friendly reporting tool out of Excel. Many thanks again.
--
BillW


"Charles Williams" wrote:

Sounds like you should be using a proper database rather than Excel (or
maybe SUMIFS in Excel 2007)

anyway here is some code to get you started

public function Bill(theData as range, OrgList as range, AcctList as range,
....
dim vData as variant
dim j as long
dim k as long
dim oOrg as range
dim oAcct as range

vData=theData
'
' vdata now contains a 2-dimensional 1-based array of the data from theRange
'
for j=1 to ubound(vData,1)
for each oOrg in OrgList
if oorg.value=vdata(j,1) then
for each oAcct.value in AcctList
if oAcct.value=vdata(j,2) then Bill=Bill+vdata(j,4)
next oAcct
endif
next oOrg
next j
end function

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"BillW" wrote in message
...
Thanks for your input, appreciate it. Was glad to find SumProduct as
potential answer, but need to keep things skinny cuz of potential size of
data.
I definitely need help w/ arrays & VBA, just learning. How do I
accomplish
the below?
Main Datasheet consists of
A B C D
1 10000 3000 10 20
2 10000 9EXP 20 30
3 12510A 10000 30 40
4 12510 3000 40 50
A1:A4 is named range Org. B1:B4 = named range Acct. C1:C4 = named range
Actl11 and D1:D4 = named range Bgt11, both columns with data to be summed.
All these named ranges will always be available for formula, VBA purposes.
Users via dialog boxes will (a) provide a named range called ROrgs with
comparison criteria to match in Org, (b) provide a named range called
RAccts
with comparison criteria to match in Acct, and (c) designate which column,
Actl11 or Bgt11, to sum records for. So say ROrgs has data of 10000 and
12510A in it, RAccts has 3000 and 10000 in it, and they want Bgt11 summed.
Answer is 60, but not sure how to get there from here using arrays in VBA.
(This is much-simplified example, actually have 5 different criteria
columns
so far, with the different combinations increasing the decision points
exponentially, but will nest things if you can get me started here.)
Thanks again for all help.

--
BillW


"Charles Williams" wrote:

And your UDF wont work properly if you refer to ranges which are not in
the
UDF parameter list.

A UDF is usually faster than SUMPRODUCT/Array formulae if you get all the
data from ranges into variant arrays using
vArr=Rng
and then process the data in the arrays.

Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"Bob Phillips" wrote in message
...
SUMPRODUCT, like array formulae, can be quite resource hungry when you
have large ranges of data, and multiple instances of the formula.

However, embedding them as evaluated statements in a UDF is adding two
extra layers of work, which can only slow them down even more.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"BillW" wrote in message
...
SumProduct & Match in a UDF

I have a w-sheet of queried data that will max at about 60 columns and
27,000 rows. Am experimenting w/ SumProduct formulas as a means to
sum
data
based on multiple criteria, but am having trouble translating them
into
VBA.
(Want to go VBA route to simplify for users). My questions:
1) Have read in the postings that SumProduct may be a memory hog, end
up
being slow for users. Is that true?? If yes, blow the below questions
off,
and kindly suggest a different VBA solution than below, pls. If the
answer
is no, this is what I'm up against:
2) Here's an example of a worksheet formula I'm currently
(successfully)
using, but am having trouble getting into VBA:
=SUMPRODUCT(--(ISNUMBER(MATCH(Org,zh6A,0))),--(ISNUMBER(MATCH(Acct,SelectedAccts,0))),Actual12)
Org = named range of approx 27,000 rows, 1 column on the main data
sheet
talked about above. Data can be either numeric or string, includes
duplicates.
zh6A = named range of approx 200 rows, 1 column on a separate sheet.
Numeric or string data, no duplicates.
Acct = another named range of approx 27,000 rows, 1 column on the main
data
sheet. Data can be either numeric or string, includes duplicates.
SelectedAccts = named range of approx 50 rows, 1 column on a separate
sheet.
Numeric or string data, no duplicates.
Actual12 = another named range of approx 27,000 rows, 1 column on the
main
data sheet. Data is numeric, no dups.
3) Here's the UDF I've come up with so far (returns the #Value error):
Function ARES(ROrgs, RAccts, DataType_Mo)
Dim Org
Org = Worksheets("Data").Range("Org")
Dim Acct
Acct = Worksheets("Data").Range("Acct")
ARES = Evaluate( "SumProduct(--(IsNumber(Match(" & Org & "," &
ROrgs &
_
",0))),--(IsNumber(Match(" & Acct & "," & RAccts & ",0)))," &
DataType_Mo
&
")")

My plan is for the users to be able to provide named ranges of data
for
the
UDF arguments (e.g., the zh6A named range for the ROrgs argument), and
then
the Org and Acct variables are named ranges that will already be
defined
in
the w-book and always used in the UDF. Thanks much for anyone's help.

--
BillW