MINIF? Like SUMIF and COUNTIF

  • Thread starter Thread starter mark.wolven
  • Start date Start date
M

mark.wolven

Is there a function that would behave similarly to the SUMIF and
COUNTIF functions to find the minimum value in Column B where Column A
equals a specified value?
 
There's not a specific function for that but you can write a formula that
uses the MIN and IF functions.

This is an array formula** :

=MIN(IF(A1:A10="x",B1:B10))

=MIN(IF(A1:A10=10,B1:B10))

=MIN(IF(A1:A10=D1,B1:B10))

If a cell in column A meets the condition and the corresponding cell in
column B is empty that will evaluate as 0 and might be evaluated as the MIN
so you'd have to test for empty cells:

=MIN(IF((A1:A10="x")*(B1:B10<>""),B1:B10))

=MIN(IF((A1:A10=10)*(B1:B10<>""),B1:B10))

=MIN(IF((A1:A10=D1)*(B1:B10<>""),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
There's not a specific function for that but you can write a formula that
uses the MIN and IF functions.

This is an array formula** :

=MIN(IF(A1:A10="x",B1:B10))

=MIN(IF(A1:A10=10,B1:B10))

=MIN(IF(A1:A10=D1,B1:B10))

If a cell in column A meets the condition and the corresponding cell in
column B is empty that will evaluate as 0 and might be evaluated as the MIN
so you'd have to test for empty cells:

=MIN(IF((A1:A10="x")*(B1:B10<>""),B1:B10))

=MIN(IF((A1:A10=10)*(B1:B10<>""),B1:B10))

=MIN(IF((A1:A10=D1)*(B1:B10<>""),B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
Click to expand...

That's exactly what I needed. Thanks.
 
Back
Top