Is there a formula can get min value of a range by certain conditi
 

Hello All:

I want to know if I can use a formula to get the min date that task ID "A"
and task type "STK"? Sample data below. I want to get 1/1/2005 as Min date
for task A and Type STK. Thanks much!!

Date Task ID Task Type
1/1/2006 A PR
3/1/2006 C STK
1/1/2005 A STK
1/1/2003 B PR
1/1/2006 A PR
1/1/2006 D PR
1/1/2006 E PR
2/1/2006 A STK


Hope you all have a nice day!

Is there a formula can get min value of a range by certain conditi
 

{=MIN((A2:A9)*(B2:B9="A")*(C2:C9="STK")+99999*((B2 :B9<"A")+(C2:C9<"STK")))}

paste the above ARRAY function without the brackets and use ctrl+shift+enter
(when done correctly Excel will surround the function in brackets
automatically).

"luvgreen" wrote:

Hello All:

I want to know if I can use a formula to get the min date that task ID "A"
and task type "STK"? Sample data below. I want to get 1/1/2005 as Min date
for task A and Type STK. Thanks much!!

Date Task ID Task Type
1/1/2006 A PR
3/1/2006 C STK
1/1/2005 A STK
1/1/2003 B PR
1/1/2006 A PR
1/1/2006 D PR
1/1/2006 E PR
2/1/2006 A STK


Hope you all have a nice day!

Is there a formula can get min value of a range by certain conditi
 

Try this:

Formula entered as an array using the key combination of CTRL,SHIFT,ENTER
(not just ENTER):

=MIN(IF((B2:B9="A")*(C2:C9="STK"),A2:A9))

Format the cell as DATE

Biff

"luvgreen" wrote in message
...
Hello All:

I want to know if I can use a formula to get the min date that task ID "A"
and task type "STK"? Sample data below. I want to get 1/1/2005 as Min date
for task A and Type STK. Thanks much!!

Date Task ID Task Type
1/1/2006 A PR
3/1/2006 C STK
1/1/2005 A STK
1/1/2003 B PR
1/1/2006 A PR
1/1/2006 D PR
1/1/2006 E PR
2/1/2006 A STK


Hope you all have a nice day!

Thank Sloth and Biff.
 

Thank Sloth and Biff.

"luvgreen" wrote:

Hello All:

I want to know if I can use a formula to get the min date that task ID "A"
and task type "STK"? Sample data below. I want to get 1/1/2005 as Min date
for task A and Type STK. Thanks much!!

Date Task ID Task Type
1/1/2006 A PR
3/1/2006 C STK
1/1/2005 A STK
1/1/2003 B PR
1/1/2006 A PR
1/1/2006 D PR
1/1/2006 E PR
2/1/2006 A STK


Hope you all have a nice day!

Is there a formula can get min value of a range by certain conditi
 

Thank both Sloth and Biff.

"luvgreen" wrote:

Hello All:

I want to know if I can use a formula to get the min date that task ID "A"
and task type "STK"? Sample data below. I want to get 1/1/2005 as Min date
for task A and Type STK. Thanks much!!

Date Task ID Task Type
1/1/2006 A PR
3/1/2006 C STK
1/1/2005 A STK
1/1/2003 B PR
1/1/2006 A PR
1/1/2006 D PR
1/1/2006 E PR
2/1/2006 A STK


Hope you all have a nice day!

Thank Sloth and Biff.
 

You're welcome!

Biff

"luvgreen" wrote in message
...
Thank Sloth and Biff.

"luvgreen" wrote:

Hello All:

I want to know if I can use a formula to get the min date that task ID
"A"
and task type "STK"? Sample data below. I want to get 1/1/2005 as Min
date
for task A and Type STK. Thanks much!!

Date Task ID Task Type
1/1/2006 A PR
3/1/2006 C STK
1/1/2005 A STK
1/1/2003 B PR
1/1/2006 A PR
1/1/2006 D PR
1/1/2006 E PR
2/1/2006 A STK


Hope you all have a nice day!