Find MIN from range based on value?

[gino59]

Hi all,

I have two columns as follows:

VCA4-DT[STATUS] and VCA4-DT[START DATE]

The Status column in the table can be COMPLETED, IN PROGRESS, or STOPPED.

What I'm trying to figure out is how to get the MIN date from the START DATE column only for those values in the STATUS column that are IN PROGRESS.

=IF(ISNUMBER(OR(OFFSET(VCA4DT_tbl[[#Headers],[STATUS]],1,0),OFFSET(VCA5DT_tbl[[#Headers],[STATUS]],1,0)))="IN PROGRESS",MIN(VCA4DT_tbl[START DATE],VCA5DT_tbl[START DATE]),"No Items")

Any ideas?

Many thanks!
Gino

 

[Matty]

Try:

=MIN(IF(STATUSRange="IN PROGRESS",STARTDATERange))

Which requires array entry.

Matty

 

[gino59]

Brilliant, Matty - many many thanks!

Cheers,
Gino

 

[gino59]

Ah #$()*&!

Sorry, Matty - looks like I found a hole in that array formula. How can I amend it so that IF there is no "IN PROGRESS" in the STATUS Column, it returns "No Items Found"?

{=MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE]))}

... works fine as long as there's at least 1 "IN PROGRESS" in the STATUS column. If there isn't the date returned is 1/0/1900. Any suggestions on how I can work this? I tried...

{=MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE]),"No Items Found")}

but that didn't work!

Many thanks!
Gino

 

[barry houdini]

One way is to do that with formatting alone (retaining Matty's original formula), custom format the cell with the formula as

m/d/yyyy;;"No Items Found"

The cell value will still be zero but it displays as "No Items Found".

Or if you want that actual value in the cell add a check with COUNTIF

=IF(COUNTIF(P_tbl[STATUS],"IN PROGRESS")>0,MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE]))},"No Items Found")

 

[scottylad2]

=if(And(P_tbl[STATUS]<>"IN PROGRESS"),"No Items Found" , MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE]))}</pre>

 

[T. Valko]

Ah #$()*&!

Sorry, Matty - looks like I found a hole in that array formula. How can I amend it so that IF there is no "IN PROGRESS" in the STATUS Column, it returns "No Items Found"?

{=MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE]))}

... works fine as long as there's at least 1 "IN PROGRESS" in the STATUS column. If there isn't the date returned is 1/0/1900. Any suggestions on how I can work this? I tried...

{=MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE]),"No Items Found")}

but that didn't work!

Many thanks!
Gino

Try this...

Still array entered:

=IF(COUNTIF(P_tbl[STATUS],"IN PROGRESS"),MIN(IF(P_tbl[STATUS]="IN PROGRESS",P_tbl[START DATE])),"No Items Found")

 

[gino59]

WOW! - Thanks everyone! T.Valko, Scottylad, and Barry - all three array formulae worked perfectly!! Eeny - meeny - miney - moe!!

I am so impressed and very grateful!

It's just very inspiring to find help like this - and the constant reminders that I don't know Jack when it comes to Excel certainly keeps me humble!

Thank you all!

Cheers,
Gino

 

[T. Valko]

WOW! - Thanks everyone! T.Valko, Scottylad, and Barry - all three array formulae worked perfectly!! Eeny - meeny - miney - moe!!

I am so impressed and very grateful!

It's just very inspiring to find help like this - and the constant reminders that I don't know Jack when it comes to Excel certainly keeps me humble!

Thank you all!

Cheers,
Gino

You're welcome. We appreciate the feedback!