I have a parts list of screws in a bunch of different assemblies. Assemblies are across the top/columns and the part numbers are listed vertically in rows to create a matrix of the number of parts for all assemblies. I also have a list of how many assemblies are to be produced in the next 20 years. I want to calculate how many of each screw will be required in the next 4, 8 and 12 years. I just calculated it manually by summing the sum of 4 years' production times the number of screws for each assembly, but this turns out to be a nasty long formula. I know there has to be an easier way, but anything I've played with doesn't work correctly.
Array Multiplication



Re: Array Multiplication
Sounds like another job for SUMPRODUCT. But can you attach a small sample workbook giving the layout and where the results need to go, please.

Re: Array Multiplication
This is a generic stripped down version (there's more part numbers, assemblies, etc.) but the layout is the same, I'm messing around with SUMPRODUCT

Re: Array Multiplication
I got it with SUMPRODUCT, thanks a lot! Very awesome

Re: Array Multiplication
Can you share your solution with us, please?

Re: Array Multiplication
I used SUMPRODUCT for each row/part number times the matrix of 4 year production

Re: Array Multiplication
Thank you, that's very clever  I was getting bogged down matching headings. You make it look simple!

Re: Array Multiplication
Yeah, I had some good tutelage. . It sure beats using something like A2*SUM(B10:B14)+.... 14 times over. Thanks again
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!