Formula Too Long Problem

**Gallegos** · 09-12-2021, 05:11 PM

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.

**menem** · 09-12-2021, 10:56 PM

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.

**Gallegos** · 09-15-2021, 11:14 PM

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!

**XLent** · 09-13-2021, 04:43 AM

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

**Gallegos** · 09-15-2021, 12:34 AM

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.

**kvsrinivasamurthy** · 09-13-2021, 06:16 AM

ARRAY formula in C3 copied to full range. It covers all 10 modules.

=IFERROR(SUM(SUMIF(OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*(ROW($1:$10)-1),COLUMNS($C3:C3)-1),">0")*TRANSPOSE(OFFSET('Parts Table'!$D$3:$M$3,MATCH($B3,'Parts Table'!$B$3:$B$30,0)-1,0))),"")

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

**kvsrinivasamurthy** · 09-13-2021, 06:34 AM

ARRAY formula replaced .
Normal formula in C3 then copied.

=IFERROR(SUMPRODUCT(MMULT(SUMIF(OFFSET('Parts Table'!$D$3,MATCH($B3,'Parts Table'!$B$3:$B$30,0)-1,COLUMN($D$3:$M$3)-COLUMN($D$3)),">0"),SUMIF(OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*(ROW($1:$10)-1),COLUMNS($C3:C3)-1),">0"))),"")

**Gallegos** · 09-15-2021, 12:27 AM

Originally Posted by kvsrinivasamurthy

ARRAY formula replaced .
Normal formula in C3 then copied.

=IFERROR(SUMPRODUCT(MMULT(SUMIF(OFFSET('Parts Table'!$D$3,MATCH($B3,'Parts Table'!$B$3:$B$30,0)-1,COLUMN($D$3:$M$3)-COLUMN($D$3)),">0"),SUMIF(OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*(ROW($1:$10)-1),COLUMNS($C3:C3)-1),">0"))),"")

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.

**Bo_Ry** · 09-13-2021, 06:57 AM

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

**Gallegos** · 09-14-2021, 10:10 PM

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.

**menem** · 09-13-2021, 09:56 PM

I was thinking for all night for somthing is missing in the file.
Then I found that in 'Units Sold by Model & Market' was not mentioned about product, it just said about model.
Is it possible for you to update more data in that sheet? Or the data are ok already?

Regards.

**Gallegos** · 09-14-2021, 06:09 PM

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.

**Gallegos** · 09-14-2021, 06:23 PM

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?

**menem** · 09-14-2021, 10:08 PM

Someone was told me to avoid INDIRECT function, can't remember why.

Regards.

**kvsrinivasamurthy** · 09-15-2021, 03:19 AM

Ref: Post#12.
In the sample file the expected result was the same as obtained from Formula.
Can you explain with example in a sample file what is expected.

**kvsrinivasamurthy** · 09-16-2021, 01:51 PM

Pl see file
In C3

=SUM(INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),1)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*0,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),2)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*1,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),3)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*2,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),4)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*3,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),5)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*4,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),6)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*5,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),7)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*6,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),8)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*7,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),9)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*8,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),10)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*9,COLUMNS($C3:C3)-1)),0)))

**Gallegos** · 09-16-2021, 11:28 PM

Originally Posted by kvsrinivasamurthy

Pl see file
In C3

=SUM(INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),1)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*0,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),2)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*1,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),3)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*2,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),4)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*3,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),5)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*4,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),6)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*5,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),7)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*6,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),8)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*7,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),9)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*8,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$10,MATCH($B3,'Parts Table'!$B$3:$B$10,0),10)*SUM(IFERROR(MMULT(1*('Parts Table'!$N$3:$AG$3>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*9,COLUMNS($C3:C3)-1)),0)))

Thank you for this revised formula. I sincerely appreciate all your hard work & dedication to
helping me. Unfortunately, I tried to see if it would work but I could not get it to work because
the formula is specifically referencing cells or a range of cells in the Parts Table worksheet.
As I had noted previously, the parts in this worksheet can change and so the number of rows
in the worksheet can change (could be more or less). So, I don't think specifically referencing
the row numbers in this worksheet will work. For instance, your formula has the following
Match function: MATCH($B3,'Parts Table'!$B$3:$B$10,0). The assumption made in this Match
function is that the maximum # of rows is 10, but it could be 5 or 50 or 1000. In the real
spreadsheet I do have a table defined in the Parts Table that would include the category,
description and base cost and so this table expands and contracts as the data does. So,
perhaps the Match function can be replaced with a table lookup? I have attached a revised
spreadsheet showing this "Parts_Core_Data" table like it is in the real spreadsheet (vs. the
example). Also, because the rows can change in the Parts Table, I believe your Index function
would need to change as well. You currently have INDEX('Parts Table'!$D$3:$M$10, but that would
need to be changed to INDEX(INDIRECT("'Parts Table'!"&"$D"&ROW()):INDIRECT("'Parts Table'!"&"$M"&ROW())
to allow the flexibility if the row changing.

**kvsrinivasamurthy** · 09-17-2021, 12:53 AM

The problem referred is solved . Here is the required formula in C3. It takes care up to 1000 rows. Index and match combination looks for selection of range properly.
If you want to change the number of rows Using Ctrl+F change 1000 to required number of rows.

=SUM(INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),1)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*0,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),2)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*1,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),3)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*2,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),4)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*3,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),5)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*4,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),6)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*5,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),7)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*6,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),8)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*7,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),9)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*8,COLUMNS($C3:C3)-1)),0))+INDEX('Parts Table'!$D$3:$M$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),10)*SUM(IFERROR(MMULT(1*(INDEX('Parts Table'!$N$3:$AG$1000,MATCH($B3,'Parts Table'!$B$3:$B$1000,0),)>0),OFFSET('Units Sold by Model & Market'!$C$3:$C$22,21*9,COLUMNS($C3:C3)-1)),0)))

I avoided INDIRECT formula. It is a passive formula and slows down the working of Excel. Vlookup formula can select only single value. Here arange of cells are to be selected. So INDEX and MATCH is used.

Formula Too Long Problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Re: Formula Too Long Problem

Thread Information

Users Browsing this Thread

Similar Threads

Substitute formula lower and upper case character and long text problem

Problem with recording a macro with long formula - works in Excel but not in macro

Long Array Formula Problem

Problem with a long formula

Formula too long problem.

[SOLVED] Formula too long problem

Problem with VBA returning the contents of a long formula.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions