Help using sumproduct to total data from two tables

[neesh0]

Hello,

I am having trouble figuring out a formula that needs to determine how many units of a "sub part" are needed to assemble a "final product" for a foretasted period of time using data from two tables.

Both tables are coming from a pivot table, so they will be dynamic and from what I believe, there is no way to consolidate or merge them. And so a formula is needed to sum the values in a third table.

The first table would be the "final product" forecasts, with the final product number on the y axis and the month it is expected to sell on the x axis. The corresponding columns would show how much of each part is expected to sell in a given month.

The second table would indicate what "sub-parts" are needed to assemble one "final product". On the y axis we would have the "final product" number and on the x axis there would be the "sub parts" needed to assemble one final part. In the screenshot example, to make one unit of Final Product A, you would need 10 units of Sub Part A and 5 parts of Sub Part B etc.

I believe a SUMPRODUCT formula is needed, but I can't get anything close to what I am looking for, so hoping I can get some help here. Ideally the output would be a table that has the forecasted "sub parts" needed and split by month on the X axis. The screenshot is a simplistic version of the data I am working with, the real application will have tens of thousands of sub parts and hundreds of final products. The data would always be dynamic as the information is being pulled from a database.

Any help at all would be very much appreciated, I feel like I am missing a big but simple piece of the puzzle so hopefully it can be done. Thank you in advance.

 

[Fazza]

welcome, neesh

with the data coming from a database, and thousands of records, sumproduct formula is likely a poor approach. probably a pivot table can do what you want - straight from the data. or a query table. in fact straight from the database if you have access to it - pivot tables and queries can take data from multiple tables (either in the database or in Excel)

I don't use power query, however I'm sure it would be well suited to the task too. maybe the best approach in Excel

your explanation is good however pasting a picture is of minimal assistance & will stop a lot of people from helping. suggest you start again with some sample data that can be copied & pasted to Excel. both sample input/starting data and the corresponding result

regards, fazza

 

[DanteAmor]

Hi and welcome to MrExcel!

Here is a small sample of the Sumproduct formula, but it works if Tables 1 and 2 are aligned as shown in the example.
But as Fazza comments, if you have thousands of records, then you are going to have thousands of formulas and your sheet would not be efficient.

Another alternative would be a macro that performs all calculations with in-memory arrays.
But you should show the real structure of your tables, that is, if they are on the same sheet, in which ranges, or if they are on different sheets, the structure of each table on each sheet.

If you put a sample of your data, use the XL2BB tool, you can see it in my signature.

varios 11may2020.xlsm
	A	B	C	D	E	F	G							N	O	P	Q
1
2	Final Product Forecast														Sub Part Summary
3	Column1	January	February	March	April	May	June								Column1	Sub Part A	Sub Part B
4	Final Product A	1	0	0	0	0	21								Final Product A	10	5
5	Final Product B	0	0	0	0	0	0								Final Product B	2	5
6	Final Product C	2	0	0	0	0	4								Final Product C	5	0
7
8
9
10	Sub Part Forecasts
11	Column1	January	February	March	April	May	June
12	Sub Part A	20	0	0	0	0	230
13	Sub Part B	5	0	0	0	0	105
Hoja7

Cell Formulas
Range	Formula
B12:G13	B12	=SUMPRODUCT((INDEX($B$4:$G$6,,MATCH(B$11,$B$3:$G$3,0)))*(INDEX($P$4:$Q$6,,MATCH($A12,$P$3:$Q$3,0))))