×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

Sumproduct Across Multiple Sheets with IFERROR
2

Sumproduct Across Multiple Sheets with IFERROR

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close