Sumproduct Formula Based on Specific Condition

[arlapin]

Hello -

I have a raw data table with multiple rows of data where each row contains multiple columns of data based on a specific location identifier. Let's call this Table 1. I have a second table with a single row of factors for the same number of columns as the first table (referred to as Table 2). I would like to create a new table (Table 3) containing a formula that: 1) finds the row of data based on the specific location identifier (condition 1) in Table 1, and 2) multiplies the data for that particular row in each column in Table 1 with the corresponding factor in Table 2. My attempts to use SUMPRODUCT, DSUM, VLOOKUP, etc. have failed miserably. Below is a visual representation of each table, and what I would like to accomplish.


Table 1

A B C D
1 10 0.5 1.5 1.0
2 11 0.2 0.2 0.5
3 12 0.3 2.5 1.0

Where col A is the location identifier, and cols B-D contain the data


Table 2

A B C
1 0.3 0.5 0.2

Where cols A-C represent the factors to be multiplied by the data in Table 1. Col A factor multiplied by Col B data point and so on.


Table 3 (Output Table)

A B
1 10 (0.5*0.3) + (1.5*0.5) + (1.0*0.2)
2 11 (0.2*0.3) + (0.2*0.5) + (0.5*0.2)
3 12 etc.


Please let me know if I can answer any questions to help clarify my question. Thanks in advance for any assistance!

[FDibbins]

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[arlapin]

I've uploaded the workbook, Test.xlsx, with the following tabs: Table 1; Table 2; Table 3. These tabs correspond with the explanations provided in the initial post. My expected outcome is to create a formula that searches the database in Table 1 based on the location identifier (TAZ), and multiplies all of the data in that particular row with the factors shown in Table 2 for each category (X, Y, Z). I have not arrived at the outcome (yet) because my formulas result in a #Value! error.

Thanks again for any help!

[FDibbins]

try this in table 3

B2 =SUMPRODUCT(--('Table 2'!B2:W2)*--('Table 1'!B2:W2))
C2 =SUMPRODUCT(--('Table 2'!B3:W3)*--('Table 1'!B2:W2))
D2 =SUMPRODUCT(--('Table 2'!B4:W4)*--('Table 1'!B2:W2))

[arlapin]

try this in table 3

B2 =SUMPRODUCT(--('Table 2'!B2:W2)*--('Table 1'!B2:W2))
C2 =SUMPRODUCT(--('Table 2'!B3:W3)*--('Table 1'!B2:W2))
D2 =SUMPRODUCT(--('Table 2'!B4:W4)*--('Table 1'!B2:W2))

Thanks for the suggestion. However, I would like a more automated solution that references the entire db in Table 1 such that the formula can be copied to all cells and I don't have to manually reference each individual TAZ (row of data). Table 3 is the summary table and contains all TAZs, although all TAZs are not listed in Table 1 (i.e., for those TAZs not listed in Table 1, the summary table would show a "0"). My preference to automate is that the data in Table 1 will change over time, with possibly additional/omitted TAZs (and changes in data) AND I have multiple other db tables similar to Table 1 for which I need to create additional summary tables.