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

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
 
B

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
 
J

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
 
J

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
 
J

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
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top