Min with multiple criteria excluding blanks

[JMMSS]

Hi everyone,

I have a spreadsheet with a list of clients, set either active/inactive, and lists their referral date.

I am setting up a summary page where i want to list the earliest referral date that is on the active list.
However i want the blanks removed from the range, and the range to be set.

sample data:

Column A Column B
1 "Active" 3/9/2019
2 "Closed" 6/7/2019


I have tried to get the earliest date, but cant work out how to exclude the blanks from the range:
my half way code was:

Please Login or Register  to view this content.

which pulls up the blanks, and gives the date of 00/01/1900.
Im just not sure where to go from here

I dont have access to Minifs from each machine so i want to not use that if possible.

Please help

[Bob Phillips]

Try

Please Login or Register  to view this content.

[JMMSS]

Hi Bob, unfortunately no change.
below is the data im using in those columns

I added a if false entry to the end of the If which displays the date 1/1/3000 for a false to the if.

Please Login or Register  to view this content.

so now after the false entry, the cell returns 1/1/3000. so its not detecting the range and criteria at the start it seems.

Please Login or Register  to view this content.

[AliGW]

Did you enter the formula with CTRL+SHIFT+ENTER?

[JMMSS]

WOW, so what does CTRL + Shift + Enter do besides enclose the formula in parentheses?

[AliGW]

It makes it work!!!

It's what is known as an 'array' formula, and without entering it that way, Excel will not look at full arrays generated within it, only the first item returned in an array.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Bob Phillips]

WOW, so what does CTRL + Shift + Enter do besides enclose the formula in parentheses?

Your original formula wouldn't work if not array-entered, so I assumed that you knew about it.