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:
[TABLE="width: 657"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Code[/TD]
[TD]Product[/TD]
[TD]Category[/TD]
[TD]Qty[/TD]
[TD]Min[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[TD]10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]D[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]D[/TD]
[TD]2
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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