SUMIF Halp!

12/31/19
Accepted

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.


Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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)

Answers

  • Paul NewcomePaul 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.

  • What would I need to add to Sheet #1?

  • Paul NewcomePaul 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.

  • 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 NewcomePaul 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.

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


  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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)

  • Index/Match worked perfect! Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Excellent! Happy to help! 👍️

Sign In or Register to comment.