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

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

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

You could use an if statement like:

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

Just Tom

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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Just Anna" wrote in message
...
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

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

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


"TomPl" wrote:

You could use an if statement like:

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

Just Tom

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

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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Just Anna" wrote in message
...
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

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

Post the exact formula that returns the error

--


Regards,


Peo Sjoblom

"Just Anna" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Just Anna" wrote in message
...
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

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

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


"Peo Sjoblom" wrote:

Post the exact formula that returns the error

--


Regards,


Peo Sjoblom

"Just Anna" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Just Anna" wrote in message
...
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

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

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.
--
Thanks,
Shane Devenshire


"Just Anna" wrote:

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


"Peo Sjoblom" wrote:

Post the exact formula that returns the error

--


Regards,


Peo Sjoblom

"Just Anna" wrote in message
...
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

"Bernard Liengme" wrote:

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Just Anna" wrote in message
...
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