Sum values from another grid based on multiple columns & values, and cross sheet row matching
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
Change Request Register (CR)
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
Best Answer
-
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!
Answers
-
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!
-
Hi @Nick Korna
Thanks for your response.
Unfortunately, when it use that formula i get the following:
I've checked to make sure there are no missing commas etc.
Kind Regards
Gavin
-
Hi @Nick Korna
Ignore my last comment - your formula worked, i had a spelling mistake 🙄
Thanks for your help, much appreciated.
Gavin
-
No problem; all's well that ends well. 😊
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!