Neede SmallIf or MinIf function

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

I have two columns, not necessary next to each other.

In these I have someting like

1 3
2 2
1 4
1 5
2 1
3 3
2 3
2 2
3 2
3 3
3 4

Now I need to find the minimum value in column 2, for a certain value, et 2
in column 1. In this case the alue im looking for is 1.

I have tried different combinations of SMALL and MIN, SUMPRODUCT and so on
with or without Arrayformulas, but I can't find the right way to do it. Any
help?

regards Jan
 
One way:

=MIN(IF(A1:A11=2,B1:B11))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).
 
Another option would be to add headers and create a pivottable.

You could drag the header for column A to the row field and drag the header for
column B to the Data field area. But then change that to Min of.

You'll end up with a quick summary of all minimums for each unique value in
column A.

Heck, you could drag the header for column B into the data field area and choose
max, sum, average, ...
 
Perfect. Thank you, just what I needed :-)

Jan

Dave said:
One way:

=MIN(IF(A1:A11=2,B1:B11))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it correctly, excel will wrap curly brackets {} around your
formula. (don't type them yourself.)

Adjust the ranges to match--but you can't use whole columns (except
in xl2007).
Click to expand...
 
Back
Top