CODEWITHSUNDEEP
excelfunctionexcel-formulacells
deepseapanda
deepseapanda

Reputation: 3907

Function to calculate the min of a range of cells using if statements in excel

I am trying to calculate the min value for a range of cells.

Cells D3:D153 contain either dry or wet, cells C3:C153 contain a number from 0 to 10.

I would like to calculate the minimum for the cells that are dry and are <=5

=MIN(IF(AND(D3:D153="Dry", C3:C153<=5)), C3:C153)

I thought the above would work but I end up with function errors.

I also have other columns such as G3:G153 which contain dry or wet and F3:F153 which contains a number from 0 to 10. If I wanted to include this in the original function how would I go about combining it?

Upvotes: 3

Views: 896

Answers (1)

mkingston
mkingston

Reputation: 2718

Try this:

{=MIN(IF((D3:D153="Dry")*(C3:C153<=5),C3:C153,11))}

When you enter it, use Ctrl+Shift+Enter to get the curly brackets (and make it into an array formula).

Edit, second part of your question:

{=MIN(IF((D3:D153="dry")*(C3:C153<=5),C3:C153,11),IF((G3:G153="dry")*(F3:F153<=5),F3:F153,11))}

Alright!

Upvotes: 2

Related Questions