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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    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,

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Ashley Pierce

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!