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
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!