Min with sumproduct

[peterkiukas]

I´m having a problem figuring this formula out. I have a formula that count how many services we have that are over 20 days old + a few other citeria. Now i´m trying to get the date of the oldest service.

This is the formula that i use to count the number of old services.

=SUMPRODUCT((G!$A$2:$A$6000=$B4)*(NOT(G!$I$2:$I$6000="Valmis/Odottaa nouto"))*(NOT(G!$I$2:$I$6000="Valmis, toimitettu"))*(NOT(G!$I$2:$I$6000="Finished/WaitingDeli"))*(Päivämääärät!$C$1-G!$C$2:$C$6000>Päivämääärät!$I$3))

I just cant figure out how to get the min function in the so that it would work and give me the date for the oldest service. Could someone please help me.

[shg]

Without seeing your data, it's hard to say. But it will be an array formula having the form,

=MIN( IF( Cond1 * Cond2 * ... * CondN, Date) )

The IF function returns False when the conditions are not all satisfied (which the MIN ignores), and the dates of the lines that do meet the conditions.

[peterkiukas]

I´m really new to all this, but is this what you ment?

=MIN( IF((G!$A$2:$A$6000=$B4)*(NOT(G!$I$2:$I$6000="Valmis/Odottaa nouto")*(NOT(G!$I$2:$I$6000="Valmis, toimitettu"))*(NOT(G!$I$2:$I$6000="Finished/WaitingDeli"))*(Päivämääärät!$C$1-G!$C$2:$C$6000>Päivämääärät!$I$3))) )

i attached a sample of my worksheet.

[shg]

Maybe

=MIN( IF( (G!$A$2:$A$6000=$B4) * (G!$I$2:$I$6000 <> "Valmis/Odottaa nouto") * (G!$I$2:$I$6000 <> "Valmis, toimitettu") * (G!$I$2:$I$6000 <> "Finished/WaitingDeli"), G!$D$2:$D$6000 ) )

It's an array formula -- you have to confirm it with Ctrl+Shift+Enter, not just Enter. If you do it right, it will be enclosed in curly braces.

BTW, time for you to learn to use dynamic named ranges. Those huge references will kill performnace, and they make your formulas unreadable.

[peterkiukas]

Works perfecty thank you!

Dynamic named ranges?

The formulas i use now in my sheet dont use dynamic ranges?
Would it be better if i changed the formulas that i now have to something like the formula
that you came up with?

Still learning by hopefully someday i´ll get there :=)

[shg]

The formulas i use now in my sheet dont use dynamic ranges?

No, you'd know if they did, because you'd have to have created them.

Take a look at http://www.ozgrid.com/Excel/DynamicRanges.htm.