MRP BOM/Need Help with sumproduct not reading the complete range.

[DiegoFG]

Hello.


I want to create a simple sheet that will add up all my Bill of materials based on my production plan per day but I have already initiated but I am struggling with the SUMPRODUCT formula, it is not adjusting to my table.

I have annexed the sheet for your viewing, please help!

Thank you!

[loginjmor]

Hi -

Part of the problem is SUMPRODUCT needs all of the arrays you are multiplying to be the same size. For example, your formula in cell C915 is:

=SUMPRODUCT(($C$3:$AV$3=$A915) * ($C$4:$C$456*C$460:C$912))

The range C3 to AV3 is 46 items. But you are multiplying by ranges that are 452 items (C4 to C456). So SUMPRODUCT doesn't know what to do with any matches beyond 46.

It's also a little weird to have a horizontal array (C3 to AV3) multiplied by a vertical array (C4 to C456). Not saying you can't do it, but I'm not sure how that works. Usually, all the arrays are the same (either vertical OR horizontal) and when SUMPRODUCT finds a match it multiplies across that row (if they are vertical arrays) or down the column (if they are horizontal arrays).

Here is a link that explains SUMPRODUCT very well.

http://xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps.

[A.Clubb]

Hi

Great question. If you're still looking for a solution, I have some VBA code that you may want to test. My own tests suggest it gives the right answers. I wrote it because I failed to get SUMPRODUCT to work. If you are confident in VBA then you can give it a try, if not then perhaps another reader can find a formula answer.

Regards
Alan

[DiegoFG]

Hi Alan,

I am not that confident with VBA but I would still like to try it, could you help me set it up?

In advance many thanks!

[A.Clubb]

Hi

Customise the ribbon so that you have Developer on the Main Menu. Click Developer, Visual Basic. Then play with Debug options, F5 runs the whole code straight, F8 lets you step through each line at a time. Be sure to have a backup copy before doing anything!

Good luck
Alan

[DiegoFG]

Hi Alan,

Sorry to bug you man but my current excel does not have VBA. Any other possible options?

Thanks for the help!

[A.Clubb]

Hi

Every Excel come with VBA as far as I know. Perhaps you have an earlier version but there will be a way to enable it. Other than VBA, I cannot see a solution using formulas to what is a very good question.

Sorry
Alan

[A.Clubb]

Hi DiegoFG

I have had another look at your problem. Try the attached as a solution without the need for VBA.

I had to remove the links for the BOM (top) section because they didn't work correctly once downloaded. You'll have to test thoroughly to ensure you get correct answers for each component, each assembly and each day.

Regards
Alan