How to use Excel 2016 maxifs function to find maximum value for specific month

Question

Good morning,

I'm using Excel 2016 to analyze stock data. I need to find the maximum value for a data range within a specific month. I know how to do this using several helper columns, but I'd like to take advantage of the "maxifs" function. I want the maximum value of the previous month to be calculated on the first date of the next month. For example, on 9/1/2010, I would like the maximum value of August 2010 to be displayed. I need the code to be dynamic so I can use any data set.

Here's an example of my code from cell C24:

=IF(MONTH($A24)<>MONTH($A23),MAXIFS(B$2:B23,A$2:A23,MONTH(A$2:A23)=MONTH(A23)),"-")

And here's a screen shot of my example spreadsheet to give some context:

Example Spreadsheet

I know I have something slightly off on the criteria1 syntax. Any help would be appreciated.

Thanks!

Answer 1

You would bracket the month:

=MAXIFS(B$2:B23,A$2:A23,">=" & EOMONTH(A23,-1) +1,A$2:A23,"<" & EOMONTH(A23,0)+1)

So your whole formula:

=IF(MONTH($A24)<>MONTH($A23),MAXIFS(B$2:B23,A$2:A23,">=" & EOMONTH(A23,-1) +1,A$2:A23,"<" & EOMONTH(A23,0)+1),"-")