Help using sumproduct to total data from two tables

neesh0

New Member
Joined
May 11, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
    Capture.PNG
    39.4 KB · Views: 8

Some videos you may like

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
Joined
May 17, 2006
Messages
9,368
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
Joined
Dec 3, 2018
Messages
12,618
Office Version
  1. 2007
Platform
  1. Windows
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))))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top