Multiple SUMIFS Statements across sheets

Richard Heath

Hello,

I'm seeking some assistance to draw data from multiple sheets, based on a number of criterion.

I'm looking to return a value based on two criterion in two different sheets to a third sheet.

Basically, IF "Yes" appears in the Payment Certificate Helper Column of this sheet (Payment Register), I need to then look up the associated Contract Number - in this case 1, in the Contract Register.

Payment Register

Contract Register

After identifying Contract Number 1, I need the formula to return Contract Sum (in this case - $0.50) to the third sheet.

Thanks for your assistance.

Regards,

Richard.

Genevieve P.

Hi Richard,

It sounds like it may be easiest to first have a helper column in your Payment Register sheet that pulls in the Contract Sum, based on its Contract Number, into that row. Then in your third sheet you could either do cell-links to the helper column in the Register or use a cross-sheet formula to pull in different information.

If you had a helper column, you could use an INDEX(MATCH formula to bring in the Contract Sum based on the Contract Number. An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

For example, using your columns:

=INDEX({Contract Sum Column}, MATCH([Contract Number]@row, {Contract Number in other sheet}))

In your case, you'd only want this to be pulled in if the YES is input, so you'd embed this INDEX(MATCH inside an IF statement. Full Formula:

=IF([Payment Certificate Helper]@row = "Yes", INDEX({Contract Sum Column}, MATCH([Contract Number]@row, {Contract Number in other sheet})), "")

Now, this will also mean that your Contract Number needs to be filled out for each row (I see currently the child rows are blank). You can have the child rows auto-populate with their parent number like so:

=PARENT()

If you need help using an INDEX(MATCH or a different cross-sheet formula to then bring together the information from the first sheet, it would be helpful to see a screen capture of this third sheet in order to understand why it's separate or how this data will be used.

Thanks!

Genevieve

Genevieve P.

Hi Richard,

It sounds like it may be easiest to first have a helper column in your Payment Register sheet that pulls in the Contract Sum, based on its Contract Number, into that row. Then in your third sheet you could either do cell-links to the helper column in the Register or use a cross-sheet formula to pull in different information.

If you had a helper column, you could use an INDEX(MATCH formula to bring in the Contract Sum based on the Contract Number. An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match in the other sheet}))

For example, using your columns:

=INDEX({Contract Sum Column}, MATCH([Contract Number]@row, {Contract Number in other sheet}))

In your case, you'd only want this to be pulled in if the YES is input, so you'd embed this INDEX(MATCH inside an IF statement. Full Formula:

=IF([Payment Certificate Helper]@row = "Yes", INDEX({Contract Sum Column}, MATCH([Contract Number]@row, {Contract Number in other sheet})), "")

Now, this will also mean that your Contract Number needs to be filled out for each row (I see currently the child rows are blank). You can have the child rows auto-populate with their parent number like so:

=PARENT()

If you need help using an INDEX(MATCH or a different cross-sheet formula to then bring together the information from the first sheet, it would be helpful to see a screen capture of this third sheet in order to understand why it's separate or how this data will be used.

Thanks!

Genevieve

Richard Heath

Hi Genevieve,

Thanks for the amazing response!

I ended up inputting a helper cells into my Metric Sheet that looks up the Contract Number based on "Yes" in the Payment Register then running a second formula to look up the returned Contract Number value and looking up my Contract Register - so two separate formulas instead of one - no big deal.

Thanks for taking the time to detail the INDEX MATCH alternative.

Genevieve P.

No problem at all! Glad that you were able to find something that works for you 🙂