SUMPRODUCT to long

i have a spreadsheet that employs dependent drop-downs. as drop-downs are selected by users the results change as more "filters" are used against data.

i'm using the following SUMPRODUCT formula to extract the results, yet now that i have to add more "filters" the formula is exceeding the 1024 limit.

CHOOSE(H$6,"",(CHOOSE(MAX($C$14:$C$20),(SUMPRODUCT(--(Tax_CalcData_SubFund=H$2),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))),(SUMPRODUCT(--(Tax_CalcData_Level2=H$2),--(Tax_CalcData_SubFund=H$3),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))),(SUMPRODUCT(--(Tax_CalcData_Level3=H$2),--(Tax_CalcData_SubFund=H$3),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))),(SUMPRODUCT(--(Tax_CalcData_Level4=H$2),--(Tax_CalcData_SubFund=H$3),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))),(SUMPRODUCT(--(Tax_CalcData_Level5=H$2),--(Tax_CalcData_SubFund=H$3),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))),(SUMPRODUCT(--(Tax_CalcData_Level6=H$2),--(Tax_CalcData_SubFund=H$3),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))),(SUMPRODUCT(--(Tax_CalcData_Level6=H$2),--(Tax_CalcData_SubFund=H$3),INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0)))))),(SUM($H33:INDIRECT(ADDRESS(ROW(),(COLUMN()-1))))))

there are common elements to the formula:

1.

INDEX(Tax_CalcData,,MATCH($F33,Tax_CalcData_Elem,0))

is used in each SUMPRODUCT.

2. although not displayed above, the formula must ensure that Level 3 looks at Level 2 and Level 3, Level 4 looks at Level 2 and Level 3 and Level 4 etc so there is repitition there (it was at this point the formula exceeded the length limit)

any suggestions on how to make this formula overcome the length limit and achieve the result?

Hi

Can you put the common part into its own cell, then use that cell reference in your full formula?

rylo

i tried that, but the common function is inherent to the SUMPRODUCT.

i was thinking DSUM yet that too is proving difficult with multiple criteria.

i may have to detail unique identifiers in the data table such that there is only one SUMPRODUCT.

agguugggh....

That should have been put the
MATCH($F33,Tax_CalcData_Elem,0)
part into a separate cell......

If that doesn't make things small enough, put up an example file with some representative data, your drop downs, named ranges etc. Small enough to work with, but the same structure as your real file so you can easily transfer any solution.


rylo