Having issues writing a 'MAXIFS' array function

I’m pretty sure I’m almost there with this but I haven’t had much experience with array formulas so I think I’m missing something really obvious.

I have a list of measurement points (time and date) and the wind speed at each of those points. For every day I want to be able to say what the maximum and minimum windspeeds were and then also what the max and min were within a specific time period (which I specify in two input cells). Essentially I am looking to create some kind of MAXIFS() and MINIFS() in excel.

I thought I could do this with an array but the formula I’ve written doesn’t seem to work…

=MAX(IF(AND($B$6:$B$56219=$B6,$C$6:$C$56219>=$C$2,$C$6:$C$56219<=$C$3),$D$6:$D$56219))

I’ve attached a sample workbook with completely random data to try and illustrate what I'm trying to do

Thanks in advance for any help!
Wind Speed Test book.xlsx

May be this...

In D2 Cell

Formula:

Please Login or Register  to view this content.

Maybe??

=SUMPRODUCT(MAX((C6:C79>=C2)*(C6:C79<=C3)*(D6:D79)))

Or ARRAY?

=MAX(IF($B$6:$B$56219=$B6,IF($C$6:$C$56219>=$C$2,IF($C$6:$C$56219<=$C$3,$D$6:$D$56219))))

you were pretty close-you just can't use AND like that in an array formula
=MAX(IF($B$6:$B$56219=$B6,IF($C$6:$C$56219>=$C$2,IF($C$6:$C$56219<=$C$3,$D$6:$D$56219))))
also ought to work

Try:

=MAX(IF($B$6:$B$56219=$B6,IF($C$6:$C$56219>=$C$2,IF($C$6:$C$56219<=$C$3,$D$6:$D$56219)))) array entered


Regards, TMS

yes-select one cell and ctrl+shift+enter the formula then drag-fill that down

Thanks for the rep.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.