SUMIF Halp!

ryan.diamond55371

So I have this issue I would love to solve. I have a Contract Database (Sheet #1) that holds a current contract value for that contract number (216116). My issue is pulling the child row value (Total Contract Value) to another sheet (Sheet #2) using the Job No as criteria. So i'm not sure if SUMIF or SUMIFS will do it because I do not think VLOOKUP could. Any help is greatly appreciated! Also, I cannot add anything to Sheet #1, i.e. helper columns, cells.

SS-1.jpg

Paul Newcome

If it is only duplicated for the rows you want to pull, it would look something like this (using the appropriate methods to create the cross sheet references).

=INDEX({Sheet 1 Fully Executed Column}, MATCH([Job No]@row, {Sheet 1 Change Order Markup Column}, 0))

This would only pull the first row where the Job Number is found. If you need to add multiple rows for the same Job Number (based on your SUMIFS comment in the original post)...

=SUMIFS({Sheet 1 Fully Executed Column}, {Sheet 1 Change Order Markup Column}, @cell = [Job No]@row)

Paul Newcome

Unfortunately it can't be done without adding to Sheet 1 outside of manual entry. To be able to do this without adding to Sheet 1, you would need to be able to use hierarchy functions in cross sheet references which is not possible.

Feel free though to Submit a Product Enhancement Request.

ryan.diamond55371

What would I need to add to Sheet #1?

Paul Newcome

You would need a column to duplicate the contract number. This column can be hidden from view once it has been set up if clutter is a concern.

ryan.diamond55371

Duplicate the contract number for all rows? Or I mean all child rows under the parent? If i do that then I would just use VLOOKUP, is that what you are thinking?

Paul Newcome

Close. I personally prefer INDEX/MATCH or INDEX/COLLECT because it is so much more flexible and it doesn't matter what order the columns are in. The contract number would only need to be duplicated on the rows that you are wanting to pull, but it can be accomplished just as easily if it is duplicated on all rows as long as there is a way to further drill down such as specific text that is always used in another column only in the rows that need pulled or something to that effect.

ryan.diamond55371

Okay I found a way to add the contract number to test it out, how would the formula look?

Annotation 2019-12-31 115938.jpg

Paul Newcome

If it is only duplicated for the rows you want to pull, it would look something like this (using the appropriate methods to create the cross sheet references).

=INDEX({Sheet 1 Fully Executed Column}, MATCH([Job No]@row, {Sheet 1 Change Order Markup Column}, 0))

This would only pull the first row where the Job Number is found. If you need to add multiple rows for the same Job Number (based on your SUMIFS comment in the original post)...

=SUMIFS({Sheet 1 Fully Executed Column}, {Sheet 1 Change Order Markup Column}, @cell = [Job No]@row)

ryan.diamond55371

Index/Match worked perfect! Thank you!

Paul Newcome

Excellent! Happy to help! 👍️