CODEWITHSUNDEEP
excelgoogle-sheets
c00000fd
c00000fd

Reputation: 22265

MAXIFS with date column as a condition

I need to calculate a largest value in a column, based on the date in another column. The condition, is for it to be in August of 2021.

I tried to use MAXIFS but I'm not sure how to specify the condition?

MAXIFS(D2:D39, B2:B39, MONTH=8, YEAR=2021)

Upvotes: 0

Views: 4051

Answers (2)

Chronocidal
Chronocidal

Reputation: 7951

=MAXIFS(D2:D39, B2:B39, ">=" & DATE(2021,8,1), B2:B39, "<" & DATE(2021,9,1))

Maximum value from Column D, where Column B is Greater Than or Equal To (i.e. "on or after") the start of August, and Column B is Less Than (i.e. "before") the start of September.

Upvotes: 1

Aresvik
Aresvik

Reputation: 4620

I don't know what your data looks like, but you could try query (Google Sheets):

=query({B:D},"select Col1 where year(Col3) = 2021 and month(Col3)+1 = 8 order by Col1 desc limit 1 label Col1 ''",1)

enter image description here

Upvotes: 0

Related Questions