Using YEAR function in MAXIFS criteria in excel

Question

I have a following spreadsheet, which contains the timestamp, a numerical result, and the time (duration) took to calculate the result.

From this I need to calculate maximum value of Duration in 2019

I tried something like this, but the function YEAR is not supported on the MAXIFS.

MAXIFS(B3:B100, YEAR(A3:A100), 2019)

Is there a way to do it using MAXIFS, or if there's a better way to do it using built-in methods. I want to avoid using Macros.

Any help/suggestion would be appreciated.

Have you tried a helper column to get just the year based on the date? — Solar Mike, Feb 03 '21 at 11:19
Yeah, I thought of that, and then it will be really easy to use MAXIFS. Thanks for providing the input. — ks2bmallik, Feb 03 '21 at 11:27

JvdV · Accepted Answer · 2021-02-03T11:53:13.070

2

You have "Duration" in column C, therefor try:

=MAXIFS(C3:C100,A3:A100,">="&DATE(2019,1,1),A3:A100,"<"&DATE(2020,1,1))

With Microsoft365, you may try:

=MAX(FILTER(C3:C100,YEAR(A3:A100)=2019))

edited Feb 03 '21 at 11:53

answered Feb 03 '21 at 11:22

JvdV

1 Answers1