Sum values from another grid based on multiple columns & values, and cross sheet row matching

Options
Gavin Seaton
Gavin Seaton ✭✭✭✭
in Formulas and Functions

Hi, im trying to get the correct formula for the following:

From the Change Request Register sum the 'Cost Impact' values that have a 'Status' of Approved or Closed. Display those total costs in the 'Approved CR (£) column for the appropriate project - where the 'M&S Job Number' rows match on both sheets. There may be several instances of a M&S Job Number in the Change Request Register that need to be summed and pulled through.

Main Sheet

image.png


Change Request Register (CR)

image.png

I'm afraid im totally lost on this so the attempted formula may be nonsense:

=SUMIFS({Change Request Register Range 3}, {Change Request Register Range 1} = "Approved", {Change Request Register Range 1} = "Closed"), + SUMIFS({Change Request Register Range 2}, [M&S Job Number]@row))

Change Request Register Range 3 = 'Cost Impact' Column

Change Request Register Range 1 = 'Status' Column

Change Request Register Range 2 = 'M&S Job Number

Thanks

Gavin

· Share on FacebookShare on Twitter

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Gavin Seaton ,

    Using your cross-sheet references - this should work for you:

    =SUMIFS({Change Request Register Range 3}, {Change Request Register Range 2}, [Job Number]@row, {Change Request Register Range 1}, "Approved") + SUMIFS({Change Request Register Range 3}, {Change Request Register Range 2}, [Job Number]@row, {Change Request Register Range 1}, "Closed")

    Hope this helps!

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!