Sumproduct Across Multiple Sheets with IFERROR 2

[BxWill]

Have an MS Excel 2017 workbook with three worksheets; "Combined", "Widgets", and "Washers."

Each worksheet has four columns; AccountNo ItemNo Estimate_Iter1 Estimate_Iter2

Worksheet "Widgets" has data such as this;

AccountNo ItemNo Estimate
5002056 500010 $650,000
5002056 500060 $290,000
5002058 500010 $45,000
5002058 500060 $98,000

Worksheet "Washers" has data such as this;

AccountNo ItemNo Estimate
6002850 500010 $2,500,000
6002850 500060 $150,000
7001500 500010 $3,000,000
7001500 500060 $240,000


Worksheet "Combined" contains all of the account numbers and Item numbers from all of the worksheets.

Objective is to populate the column "Estimate" on the worksheet "Combined" - extracting the data from the appropriate worksheet.

Using the following formula, I am able to extract the data onto the "Combined" worksheet from the "Widgets" worksheet.

'=SUMPRODUCT((AccountNoWidgets=B3)*(ItemNoWidgets=C3)*(EstimateWidgets))

But, the latest formula does not work

=IFERROR(SUMPRODUCT((AccountNoWidgets=B7)*(ItemNoWidgets=C7)*(EstimateWidgets)),SUMPRODUCT((AccountNoWashers=B7)*(ItemNoWashers=C7)*(EstimateWashers)))

Any insight as to a solution?

 

[combo]

If you mean excel 2016 or higher for windows, I would switch to Get&Fransform queries. Maybe what you plan to do can be done with formulas, but if your data grow, they will be slow, a new column to calculate will be a new challenge.

To work with G&T queries:
- convert entry data to tables (Widgets, Washers), rename defaults to more meaningful,
- query each table add columns with "Widgets" and "Washers" resp. if necessary, without output to worksheet (entry queries for future processing),
- start new query in G&T desktop, starting from any of the queries above, in a single query, which is a set of configurable transformations, you can perform calculations, merge queries, join queries (inner, outer, anti-inner, anti-outer) etc., with final output to table or a source for pivot table.

Your first formula can be a result of multiplication of two columns and aggregation with sum for AccountNo.
The second one - after aggregation for two tables (two queries) external join, expanding joined table, in new column conditional (if null) picking of data, deleting helper columns and shaping output data structure.

Entry data tables can expand automatically with new data, output table recalculated after refresh. Just input and output, no worksheet formulas, all processed in queries or pivot tables.

combo

 

[SkipVought]

Each worksheet has four columns; AccountNo ItemNo Estimate_Iter1 Estimate_Iter2

Worksheet "Widgets" has data such as this;

AccountNo ItemNo Estimate

Worksheet "Washers" has data such as this;

AccountNo ItemNo Estimate

???

Your biggest design mistake is chopping up your perfectly good data into separate sheets/tables.

YYY???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[BxWill]

I currently do not have any control on the design of the workbooks.

Actually, there are over 25 worksheets! For this example, I indicated that there are 3 worksheets just to simplify. I will apply the logic accordingly to my actual case.

Really think that this is a rather complex formula involving three functions such as Sumproduct, Sumif and Indirect.

Similar to something like

=SUMPRODUCT(SUMIFS(INDIRECT("'"&MySheets&"'C:C"=A7),INDIRECT("'"&MySheets&"'D:D"=B7),INDIRECT("'"&MySheets&"'I:I",FALSE)))

Where "MySheets" is the range name that contains the worksheet names.

However, I receive a REF error.

Any insight is appreciated as I am a novice in regards to the indirect function.

 

[combo]

If it is possible to change sheets with data and the updated sheets have the same data structure, I can repeat: process the data with Get&Transform queries (i.e. Power Query) instead.
You can concentarte on shaping the data instead, no problem with aggregating tables with additional info, if you need.
Maybe it is possible to complete the task with formulas, but with 25 sheets it is hard to debug; what will you do if you get one row of data more in some tables? Excel has its limitations too!


combo