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

  • Thread starter Thread starter Just Anna
  • Start date Start date
J

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
 
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
 
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
 
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
 
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
 
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.
 
Back
Top