AverageIF formulas - How can the same be done but for MAXIF or MINIF?

MAXIF or MINIF formulas don't seem to be a thing on excel?

I basically have a set of data that looks like this:

rWsH4M1.png

Now, I have used a COUNTIF formula to count the number of entires for each day. I have also done a AVERAGEIF statement to take the average 'downtime' for a particular day.

However, is there a way I can make a formula that will find the max or min value of downtime for a given day?

If it helps at all the formula I'm using for my averageif is this:

PHP Code: 

=AVERAGEIF($A$2:$A$5997,Q2,$C$2:$C$5997) 


Where the dates for all my data points are in column A, the downtimes are in column C, and in column Q I basically have a column of dates starting at the earliest date in my dataset and then going up by 1 day for each row.

Sorry if I haven't explained this well - please ask if you'd like me to clarify anything. I'd hugely appreciate any help anyone can give!

There is no built in Maxif or Minif functions..

but you can do it with an array formula like
=MAX(IF($A$2:$A$5997=Q2,$C$2:$C$5997))

And

=MIN(IF($A$2:$A$5997=Q2,$C$2:$C$5997))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

It can be problematic with the MIN formula if there are blanks in the range $C$2:$C$5997.
Blanks will be considered Zero, and the formula would return 0 as the minimum value.

So you'd have to add a criteria to test for NOT Blank
=MIN(IF($A$2:$A$5997=Q2,IF($C$2:$C$5997<>"",$C$2:$C$5997)))

Originally Posted by Jonmo1

It can be problematic with the MIN formula if there are blanks in the range $C$2:$C$5997.
Blanks will be considered Zero, and the formula would return 0 as the minimum value.

So you'd have to add a criteria to test for NOT Blank
=MIN(IF($A$2:$A$5997=Q2,IF($C$2:$C$5997<>"",$C$2:$C$5997)))

Works perfectly - thank you so much!

You're welcome.