Min and Max Look Up

[Nanogirl21]

I am using the below formula to find the average sum that matches the criteria. I am trying to adjust to create 2 new formulas for max and min based on criteria, but it is not working. I get #SPILL error. Can someone please help?

Using to find the average
=AVERAGEIF(S6:S221,B18,V6:V221)

I tried
=MAXIFS(S6:S221,B18,X6:X221)
=MINIFS(S6:S221,B18,X6:X221)

The criteria search term is in B18
The search term range is S6:S221
The Min and Max numbers are in X6:X221

Thank you.

 

[kvsrinivasamurthy]

MAXIFS not required. Try using MAXIF.

 

[Nanogirl21]

MAXIFS not required. Try using MAXIF.

MAXIF and MINIF isn't recognized by Expel. It returns the #NAME error.

 

[arthurbr]

Perhaps

=aggregate(14,6,(1/(S6:S221)=B18)*X6:X221,1)

for the max value
and

=aggregate(15,6,(1/(S6:S221)=B18)*X6:X221,1)

for the min value

 

[arthurbr]

MAXIF and MINIF isn't recognized by Expel. It returns the #NAME error.

They are part of O365 so should be recognized

 

[Fluff]

There is no such thing as maxif or minif, just maxifs & minifs.

 

[Fluff]

but it is not working. I get #SPILL error.

You have the arguments the wrong way round, it should be max range then criteria range