How to calculate Max or Min with multiple if conditions

[oxygn]

Hi All,

I am trying to figure out how to calculate max or min with multiple if conditions.

Here is sample data:

Code	Product	Category	Qty	Min
1	1	Z	10	8
2	1	Z	9
3	1	Z	8
4	1	Z
5	2	D	1
6	2	D	2

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The condition is get the minimum Qty where Product is 1 and Category is Z. The answer should be 8.

However if I use the formula =MIN(IF(AND(Products=1, C2:C7 = "Z"), Qty)) I get answer 1.

Can you please help.

Regards

 

[oxygn]

I tried =MIN(IF(AND(Products=1, C2:C7 = "Z", Qty <> ""), Qty)) and now the answer is 0

 

[facethegod]

I tried =MIN(IF(AND(Products=1, C2:C7 = "Z", Qty <> ""), Qty)) and now the answer is 0

You were almost there. Try:

=MIN(IF(Products=1,IF(C2:C7="Z",IF(D2:D7<>"",D2:D7))))

This is an array formula. So you must confirm it with Control+Shift+Enter, not just Enter.

Hope this helps

 

[oxygn]

Hahaha, Thanks mate, that did it