# Help using sumproduct to total data from two tables

#### neesh0

##### New Member
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.

#### Attachments

• Capture.PNG
39.4 KB · Views: 8

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Fazza

##### MrExcel MVP
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

##### Well-known Member
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
ABCDEFGNOPQ
1
2Final Product ForecastSub Part Summary
3Column1JanuaryFebruaryMarchAprilMayJuneColumn1Sub Part ASub Part B
4Final Product A1000021Final Product A105
5Final Product B000000Final Product B25
6Final Product C200004Final Product C50
7
8
9
10Sub Part Forecasts
11Column1JanuaryFebruaryMarchAprilMayJune
12Sub Part A200000230
13Sub Part B50000105
Hoja7
Cell Formulas
RangeFormula
B12:G13B12=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))))

Replies
2
Views
82
Replies
4
Views
105
Replies
0
Views
306
Replies
3
Views
74
Replies
1
Views
245

1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back