Formula to get the Year of max record with condition

[yllew]

Hi,

I would like to get the year of past highest record (column F) excluding Year 2021 from the "Raw" sheet, without using the pivot table helper, i appreciate if anyone can help.

[menem]

Please try

F4

Please Login or Register  to view this content.

Follow with Ctrl-Shift-Enter for array formula.

Regards.

[Bo_Ry]

Please try at

F4
=MOD(MAX(INDEX(ROUND(SUMIFS(Raw!$F$2:$F$1185,Raw!$E$2:$E$1185,B4,Raw!$C$2:$C$1185,C4,Raw!$A$2:$A$1185,ROW(Z$2010:Z$2021),Raw!$A$2:$A$1185,"<>2021")*10^6,)+ROW(Z$2010:Z$2021),)),10^4)

F15
=MOD(MAX(INDEX(ROUND(SUMIFS(Raw!$F$2:$F$1185,Raw!$E$2:$E$1185,B15,Raw!$D$2:$D$1185,C15,Raw!$A$2:$A$1185,ROW(Z$2010:Z$2021),Raw!$A$2:$A$1185,"<>2021")*10^6,)+ROW(Z$2010:Z$2021),)),10^4)

[alansidman]

With Power Query, here is the Max per year. With this methodology, you can do the same for quarter, month or half by adding each to the query.

Please Login or Register  to view this content.

[yllew]

Please try

F4

Please Login or Register  to view this content.

Follow with Ctrl-Shift-Enter for array formula.

Regards.

Thank you, it works for me

[yllew]

Please try at

F4
=MOD(MAX(INDEX(ROUND(SUMIFS(Raw!$F$2:$F$1185,Raw!$E$2:$E$1185,B4,Raw!$C$2:$C$1185,C4,Raw!$A$2:$A$1185,ROW(Z$2010:Z$2021),Raw!$A$2:$A$1185,"<>2021")*10^6,)+ROW(Z$2010:Z$2021),)),10^4)

F15
=MOD(MAX(INDEX(ROUND(SUMIFS(Raw!$F$2:$F$1185,Raw!$E$2:$E$1185,B15,Raw!$D$2:$D$1185,C15,Raw!$A$2:$A$1185,ROW(Z$2010:Z$2021),Raw!$A$2:$A$1185,"<>2021")*10^6,)+ROW(Z$2010:Z$2021),)),10^4)

The data is what i need, but the formula is pretty complicated for me, i would like to learn and try to understand, thank you so much!