Sumproduct(MIN(( returns zero

[tomass]

Hi All,

I have following table:

A B C
1 23 45
1 2 44
1 2 23
1 3 24
1 23 55
1 24 55
2 24 66
2 22 65

With the help of following formula i can find the Max value in column c that meets criterias for coulmn A and B.
=SUMPRODUCT(MAX(($A$1:$A$8=$A$11)*($B$1:$B$8=$A$12)*($C$1:$C$8)))

This formula works fine with the max function but when I want to use the same formula for the MIN function it only returns 0


Any advice someone ?


best regards
tomas

 

[barry houdini]

Hello tomas, because of the multiplication the FALSE results give you zeroes, hence zero returned for the minimum - try this "array formula"

=MIN(IF(($A$1:$A$8=$A$11)*($B$1:$B$8=$A$12),$C$1:$C$8))

confirmed with CTRL+SHIFT+ENTER

 

[tomass]

Thanks Barry!