Sumproduct min max array

[William Rivero]

Hi guys, I've been looking for a way to use sumproduct in a formula which has to satisfy two criteria before it determines the min or max of a range/array of numbers.

Problem: =SUMPRODUCT(--(Criteria1="Y"),--(Criteria2="Y"),--(MIN(A1:A100)))

But this somehow doesn't work. How can I find the min and max of those numbers that satisfy the conditions set by criteria1 and criteria2?

Stuck. Please help me out.

 

[Matty]

Try like this:

=MIN(IF(Criteria1="Y",IF(Criteria2="Y",A1:A100)))

Which is an array formula and therefore needs committing with CTRL+SHIFT+ENTER.

Change MIN to MAX for the Max...

Matty

 

[William Rivero]

Thanks, Matty, worked!