G
Guest
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.
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.