Formula Too Long Problem

without changing your existing file {per request} the attached should work, and is non-volatile

Formula:

'Parts Sold Table'!C3:

SUM(CHOOSE(1+COLUMN('Parts Table'!$D8:$M8)-COLUMN('Parts Table'!$D8),'Units Sold by Model & Market'!C$3:C$22*'Parts Table'!$D3,'Units Sold by Model & Market'!C$24:C$43*'Parts Table'!$E3,'Units Sold by Model & Market'!C$45:C$64*'Parts Table'!$F3,'Units Sold by Model & Market'!C$66:C$85*'Parts Table'!$G3,'Units Sold by Model & Market'!C$87:C$106*'Parts Table'!$H3,'Units Sold by Model & Market'!C$108:C$127*'Parts Table'!$I3,'Units Sold by Model & Market'!C$129:C$148*'Parts Table'!$J3,'Units Sold by Model & Market'!C$150:C$169*'Parts Table'!$K3,'Units Sold by Model & Market'!C$171:C$190*'Parts Table'!$L3,'Units Sold by Model & Market'!C$192:C$211*'Parts Table'!$M3)*SIGN(TRANSPOSE('Parts Table'!$N3:$AG3)))

confirmed with CTRL + SHIFT + ENTER

applied to results matrix C3:N4

if you wanted to apply the % (to quantity) simply remove the SIGN wrapper

Originally Posted by XLent

without changing your existing file {per request} the attached should work, and is non-volatile

Formula:

'Parts Sold Table'!C3:

SUM(CHOOSE(1+COLUMN('Parts Table'!$D8:$M8)-COLUMN('Parts Table'!$D8),'Units Sold by Model & Market'!C$3:C$22*'Parts Table'!$D3,'Units Sold by Model & Market'!C$24:C$43*'Parts Table'!$E3,'Units Sold by Model & Market'!C$45:C$64*'Parts Table'!$F3,'Units Sold by Model & Market'!C$66:C$85*'Parts Table'!$G3,'Units Sold by Model & Market'!C$87:C$106*'Parts Table'!$H3,'Units Sold by Model & Market'!C$108:C$127*'Parts Table'!$I3,'Units Sold by Model & Market'!C$129:C$148*'Parts Table'!$J3,'Units Sold by Model & Market'!C$150:C$169*'Parts Table'!$K3,'Units Sold by Model & Market'!C$171:C$190*'Parts Table'!$L3,'Units Sold by Model & Market'!C$192:C$211*'Parts Table'!$M3)*SIGN(TRANSPOSE('Parts Table'!$N3:$AG3)))

confirmed with CTRL + SHIFT + ENTER

applied to results matrix C3:N4

if you wanted to apply the % (to quantity) simply remove the SIGN wrapper

This formula worked great until I tried to use the Indirect function for the value 'Parts Table'!$D3
when multiplying by 'Units Sold by Model & Market'!$C3:$C22. Excel was trying to interpret the
value as an array when using the Indirect function vs. the specific value in cell $D3 in the Parts
Table worksheet. Everything until that point was working great.