Max Cost per Year
I need to determine the max Remediation Cost PER Year. I also, need to pull this information into another sheet's cell. What's the best way to go about this? This is the formula I have thus far, but it's coming out UNPARSEABLE...
=MAX(IF([Date of Incident]:[Date of Incident], <=DATE(2020, 12, 31), [Date of Incident]:[Date of Incident], >=DATE(2020, 1, 1), AND [Total Remediation Cost]:[Total Remediation Cost]))
Answers
-
Hey @Ashley Pierce
Try this.
If the sheet you're pulling into has the years listed on separate rows, you can would replace the year number with your cell reference (eg, @cell = 2021 replaced with [your target sheet column]@row )
=MAX(COLLECT({project source sheet Total Remediation Cost column}, {project source sheet Date of Incident column}, ISDATE(@cell), {project source sheet Date of Incident column}, YEAR(@cell) = 2021))
Remember since this uses cross sheet references you cannot simply copy-paste the formula into your target sheet, you must build the cross sheet references individually. Rename the cross sheet references as you wish - as a good practice rename the references to reflect the name of the column before pressing the INSERT REFERENCE button.
cheers,
-
I see that you marked KDM's response as not answering your question. Are you able to explain what didn't work for you? Are you receiving an error message or an incorrect output?
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!