Thread: Want to Use Sumproduct with Min function.

Dear all
I am working with an Excel Sheet, where I want to find out Minimum, Maximum, and Average Number of a specific Column, given a specific criteria.
I am using the following formula and am able to get the Maximum Value from a specific column.
Now when I try to find Minimum, it always give me Zero Value.

The formula which is displaying the Maximum Value is this..
{=MAX((Sheet1!$D$3:$D$503>=(--$K$4))*((Sheet!!$D$3:$D$503<=(--$Q$4))*((Sheet1!$F$3:$F$503=$D$4))*(Sheet1!$E$3:$E$503=C15)*(Sheet1!$AM$3:$AM$503)))}
Where:
$K$4 and $Q$4 are dates (To search between dates)
$D$4 is first criteria
C15 is second criteria
and $AM$3:$AM$503 is the column from where its getting the maximum value.

When I use the same above formula with MIN, the result is always Zero, which is not correct according to my data.

Thanks in advance for help.

Thanks for your response Fotis.
Please see attached

Your MIN function is returning a zero, because any rows in the array that don't meet all of the criteria return a zero value, so you need to use an IF statement to return a higher value than any you'll encounter elsewhere in the array.

This function does that:

{=MIN(INDEX(IF((MainData!$B$2:$B$31>=$D$4)*(MainData!$B$2:$B$31<=$D$5)*(MainData!$D$2:$D$31=$D$3)*(L stPQ=C8)>0,MainData!$F$2:$F$31,MAX(MainData!$F$2:$F$31)+1),0))}

However, that will return a value of 90 when there are no matches in the data set. To get around this we can use a COUNTIFS to check that the number of matches is greater than zero...

{=IF(COUNTIFS(MainData!$B$2:$B$31,">=" & $D$4,MainData!$B$2:$B$31,"<=" & $D$5, MainData!$D$2:$D$31,$D$3,LstPQ,C8)=0,0,MIN(INDEX(IF((MainData!$B$2:$B$31>=$D$4)*(MainData!$B$2:$B$31 <=$D$5)*(MainData!$D$2:$D$31=$D$3)*(LstPQ=C8)>0,MainData!$F$2:$F$31,MAX(MainData!$F$2:$F$31)+1),0))) }

That seems to work fine.

Both of these are array formula and must be entered using Ctrl-Shift-Enter.

Hope this helps.

Thank you both Andrew and OnErrorGoto0
Both solutions worked perfectly.
I was banging my head with Sumproduct, cuz I already got Max Value from it.