Is there any funcion like a @maxif or @minif?

[Just Anna]

I need to calculate the max and the min of a certain column (say B) as long
as the name in Col A matches a certain value. Basically it would have to do
the equivalent to @sumif. I can't find any function that does that... Is
there any? In alternative, can I use subtotals but in such a way that the
results are in a different sheet, not in the same sheet as the data?
Thanks in advance for your help!
Regards,
Just Anna

 

[TomPl]

You could use an if statement like:

=IF(A1="Hello",MAX(B:B),"SkipIt")

Just Tom

 

[Bernard Liengme]

It a matter of 'rolling your own'; you need to modify MAX to do what you
want
This worked for me: =MAX(IF(A1:A15="a",B1:B15,""))
In A col I had letters like 'a', 'b', 'c'
In B col I have numbers
The formula found the largest value in B col when the corresponding A col
entry was 'a'
NOTE: this is an array function and must be entered with CTRL+SHIFT+ENTER,
not with just ENTER
Excel will enclose your formula within { } in the Formula Bar
best wishes

 

[Just Anna]

Thanks for your quick answer, Just Tom, but I'm afraid it didn't work. May be
because instead of "Hello" I have a cell (it's a huge list of cels for which
I need to calculate max and mins), not a value...

Thanks anyway...

R,
Just Anna

 

[Just Anna]

Hi Bernard,

I'm afraid it keeps returning #VALUE. I had tried this before posting this
question, and have just tried again. Sorry, still no luck...

Thanks anyway,
Just Anna

 

[Peo Sjoblom]

Post the exact formula that returns the error

--


Regards,


Peo Sjoblom

 

[Just Anna]

Sorry... I've just realised I had an extra argument in the if function...IT
DOES WORK!!!!

Thank you Just Tom, Bernard and Peo!

Regards,
Just Anna

 

[ShaneDevenshire]

Hi,

And going back to your original question:

=MIN(IF(A1:A15="a",B1:B15,""))

FYI, Excel added a new function in 2007 called AVERAGEIF but unfortunately
not the MINIF and MAXIF. They also added SUMIFS, AVERAGEIFS, and COUNTIFS,
note the "S". But probably the most important new function is IFERROR.