Formula Too Long Problem

Hello fellow members. I am having a problem with a formula too long issue that I am hoping you
can help me with. I have a table of parts and within that table there can be 10 different models
and it can be sold in 20 different markets.

This table structure is used extensively throughout the workbook, so I am hoping I don't have to
change it in order to accomplish what I need to, which is to sum the total # of parts sold by month
for each model & each market the part is sold. Not every model may use the part and so the
user would just enter a zero for the quantity in the table under that model.

The part cost may be higher or lower than the base cost in each of the 20 markets sold and so the
user can enter a % increase/decrease for each of the 20 markets. However, not every market will
sell the part & so my thought was to have the user enter a 0% in the market increase % to indicate
this.

The problem is that when I multiply the quantity used by each of the ten models for each of the
20 markets (if the % of the market is zero) I get the error the formula is too long. I have tried to
come up with a different methodolgy, but so far have not been able to come up with anything &
still accomplish what I need to without changing the base structure of the table.

I have attached an Excel workbook as an example. The formula that is highlighted in the Parts
Sold Table shows the formula for just two models and two markets. If this formula is expanded to
include the ten models & 20 markets I need Excel indicates it's too long.

I look forward to any assistance you can provide me.

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

Formula:

Please Login or Register  to view this content.

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

Thank you for your reply. The data in the "Units Sold by Model & Market" was provided as an
example & I was thinking that it would be sufficient to provide the concept of what I was trying
to accomplish. The only additional data that would really need to be added is more values for
each of the months across the columns and the # of units sold in models 3 through 10. But, that
would merely be an expansion of the data & the resulting formula in cell C3 in the Parts Sold
worksheet would be too large.

I would like to thank everyone for the replies. One of the aspects of the formula in cell C3 in
the Parts Sold Table worksheet is my usage of the "Indirect" function. The reason I use this
is because the "Parts Table" in my real model (vs. the example I provided) can expand and
contract if the user adds or deletes parts & thus the row number for a particular part could
change. Thus, I think I am going to need to keep this aspect of the formula. Could the
recommended formulas be restructured to include the usage of the "Indirect" function?

Originally Posted by Bo_Ry

Please try at C3

=SUM(MMULT(TRANSPOSE(--'Units Sold by Model & Market'!C$3:C$211),--(INT((ROW($A$3:$A$211)-ROW($A$3))/21)=COLUMN($A$1:$J$1)-1))*'Parts Table'!$D3:$M3)

Ctrl+Shift+Enter

or

=MMULT('Parts Table'!$D3:$M3,MMULT(--(INT((COLUMN($A$1:$HA$1)-1)/21)=ROW($A$1:$A$10)-1),--'Units Sold by Model & Market'!C$3:C$211))

normal enter

Bo_Ry, thank you for this formula. I need to only count those markets where the part is used. Thus, if the user
enters a zero in the "Increase %" for a specific market then I don't want to count the sales in that market. I
think this formula is counting all of the sales irrespective of having a zero in the "Increase %". If I enter your
formula it provides me the same answer that my example did. However, if I place a zero into the "Increase %"
for market 1, the value counted in cell C3 of the Parts Sold Table does not change. Can you please confirm.
Thank you.

Originally Posted by kvsrinivasamurthy

ARRAY formula replaced .
Normal formula in C3 then copied.

Please Login or Register  to view this content.

Thank you for this formula. The resulting value equals the total sold, but it does not seem
to not count a market if the "Increase %" in that market in the Parts Table is zero, which
means to not count the number sold for that market from the Units Sold by Model & Market.
Can you please confirm. Thank you.

Originally Posted by XLent

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

Formula:

Please Login or Register  to view this content.

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.