Formula Too Long Problem

Originally Posted by menem

Maybe.

=SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$D$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column3]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$E$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column4]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$F$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column5]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$G$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column6]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$H$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column7]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$I$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column8]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$J$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column9]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$K$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column10]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$L$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column11]
+SUMPRODUCT(OFFSET('Units Sold by Model & Market'!$A$1,MATCH('Parts Table'!$M$1,'Units Sold by Model & Market'!$A:$A,0),MATCH(C$2,'Units Sold by Model & Market'!$1:$1,0)-1,COUNTA('Parts Table'!$N$1:$AG$1),1)*(TRANSPOSE(Parts[@[Column13]:[Column32]])>0))*Parts[@Column12]

Follow with Ctrl-Shift-Enter for Array formula.

Regards.

Thank you for this formula. This works except for one aspect that I am hoping you can
help me with. As I noted previously, I need to use the "Indirect" function in Excel to
reference the row as the row can change if the user deletes or adds parts to the "Parts
Table" worksheet. So, I need to use the Indirect function when using the Transpose function
in your formula, but Excel provides me an error when I try to enter the following:

(TRANSPOSE(INDIRECT("'Parts Table'!"&"$N"&ROW()):INDIRECT("'Parts Table'!"&"$AG"&ROW()))>0))

I have researched & researched for solutions & have tried various methods, but nothing works.
Can anyone provide me a solution? Thank you!