×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Sumproduct Across Multiple Sheets with IFERROR2

## Sumproduct Across Multiple Sheets with IFERROR

(OP)
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.

#### CODE

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

But, the latest formula does not work

#### CODE

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

Any insight as to a solution?

### RE: Sumproduct Across Multiple Sheets with IFERROR

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

### RE: Sumproduct Across Multiple Sheets with IFERROR

#### Quote:

Each worksheet has four columns; AccountNo ItemNo Estimate_Iter1 Estimate_Iter2

#### Quote:

Worksheet "Widgets" has data such as this;

AccountNo ItemNo Estimate

#### Quote:

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,

Just traded in my OLD subtlety...
for a NUance!

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

### RE: Sumproduct Across Multiple Sheets with IFERROR

(OP)
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

#### CODE

=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.

### RE: Sumproduct Across Multiple Sheets with IFERROR

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!