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,

Genevieve P.

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