Array SUM or SUMPRODUCT function error

[KratosZG]

Hi!

I have been working on implementing MRP Excel solution in a company I work for. I have already done one, and now I was working on a new more detailed system. My company prohibits me to install XLB2BB add-in so I will do my best to explain everything.

First sheet 'ProdBOMs' contains Bill of Materials for all products:

Product code	Product name	Material code	Material name	Qty
PROD1	Product 1	MAT1	Material 1	1​
PROD1	Product 1	MAT2	Material 2	2​
PROD1	Product 1	MAT3	Material 3	3​
PROD1	Product 1	MAT4	Material 4	4​
PROD1	Product 1	MAT5	Material 5	5​
PROD2	Product 2	MAT1	Material 1	10​
PROD2	Product 2	MAT2	Material 2	8​
PROD2	Product 2	MAT3	Material 3	6​
PROD2	Product 2	MAT4	Material 4	4​
PROD2	Product 2	MAT5	Material 5	2​
PROD3	Product 3	MAT1	Material 1	20​
PROD3	Product 3	MAT2	Material 2	30​
PROD3	Product 3	MAT3	Material 3	40​
PROD3	Product 3	MAT4	Material 4	50​
PROD3	Product 3	MAT5	Material 5	60​

Second sheet 'Plan' contains production plan by products and by weeks:

Product code	Product name	W03/24	W04/24	W05/24	W06/24	W07/24	W08/24	W09/24	W10/24
PROD1	Product 1	10​	15​		100​
PROD2	Product 2		20​	35​			100​		10​
PROD3	Product 3	10​	30​		20​			100​

Third sheet 'BOMs' contains BOMs matrix with materials on left side and products on upper side. That is a matrix derived from first sheet 'ProdBOMs' (first 5 columns).

PROD1

PROD2

PROD3

15.1.2024​

22.1.2024​

29.1.2024​

5.2.2024​

12.2.2024​

19.2.2024​

26.2.2024​

4.3.2024​

Product 1

Product 2

Product 3

W03/24

W04/24

W05/24

W06/24

W07/24

W08/24

W09/24

W10/24

MAT1

Material 1

1

10

20

0​

(formula) 10​

15​

0​

100​

0​

0​

0​

MAT2

Material 2

2

8

30

0​

20​

30​

0​

200​

0​

0​

0​

MAT3

Material 3

3

6

40

0​

30​

45​

0​

300​

0​

0​

0​

MAT4

Material 4

4

4

50

0​

40​

60​

0​

400​

0​

0​

0​

MAT5

Material 5

5

2

60

0​

50​

75​

0​

500​

0​

0​

0​

On the right side of that matrix (next columns) is the calculation of how much of each material we need to produce planned products by weeks. That part is where I need your help. The formula that is in the cell for W04/24 and MAT1 (marked with word "formula" in upper table) is:

=SUM(INDEX(PPlan;MATCH(BOMsProducts;PPlanProducts;0);MATCH(H$2;PPlanWeeks;0))*INDEX(BOMsSummary;ROW()-2;MATCH(BOMsProducts;PPlanProducts;0)))

The formula is array CSE (Ctrl+Shift+Enter) formula, even though Excel recognizes automatically in another older table this formula as array formula and I can just "Enter" formula without "{}" and "CSE"

PPlan = Plan!$C$2:$J$4
BOMsProducts = BOMs!$C$1:$E$1
PPlanProducts = Plan!$A$2:$A$4
PPlanWeeks = Plan!$C$1:$J$1
BOMsSummary = BOMs!$C$3:$E$7

The result should be 810, because we use MAT 1 in all 3 Products and sum of the multiplication of two array is ((10*1) + (20*10) + (30+20)) is 810.

I have tried to take parts: first array and second array and then SUM them without CSE and with CSE and it does work. I have also tried SUMPRODUCT with and without CSE and it works. But when I try to combine two arrays in one array formula it does not work, even though as I said works in older larger table. I must be missing something, and I just can't figure it out...

Please help!
Thank you!

 

[KratosZG]

I am not sure what happend during my explanation but now I got it working. I have one possible solution/explanation but I have tested it. It didn't work and that is why I have posted my problem here.

Possible explanation: On every named range I have added 1 column (if the range represent row values) or 1 row (if the range represented the column range) so in the future I could easly add more products or materials whenever I needed without expanding manually the named range. Now I think that when I reduced named range as explained in the first post everything works. Because since the last values of all named ranges were empty the formulas probably took that as some kind of error (?) Could that really be the issue?

Another related explanation: If I leave named ranged expanded for 1 row/column I only have to fill with whichever sign (can be "x", just not to leave the cell empty) in named ranges BOMsProducts and PPlanProducts and formula magicaly works even without CSE.

Can anyone explain this a little bit so everyone can learn something
Thank you!