Max Cost per Year

Ashley Pierce

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]))

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,