Min If Dates - Ignore Blanks/Zeroes - No Array Formula??

Hello -

I have the following formula:

Please Login or Register  to view this content.

Where I'm looking to have a value in cell C2 and pull the earliest date from column B where there may be multiple instances of the value in C2 in column A. However, there are some blanks and some non-blanks in column B as it relates to the value in C2. So, of course, the MIN is pulling in the blanks as 0 and thus giving me the earliest date of 1/0/1900.

I have thousands of lines of data and need to stay away from array formulas as the calculations kill it. Any suggestion on how to update the formula to accomplish what I'm looking for? Or - is there another formula that you can suggest that works with dates? I've searched and have come up with several MIN IF formulas, but most of them appear to handle numbers...

Thanks in advance for any assistance anyone is able to provide! It's very much appreciated.

Try:

=SMALL(INDEX((C2=A2:A5)*B2:B5,),COUNTIF(A2:A5,"<>"&C2)+1)

Hi Phuocam,

That worked for one of my workbooks. It appears if the data is on the same worksheet the formula works well. But if the "lookup" value (the value in C2) and the formula itself aren't in the same worksheet as the data being looked up against it doesn't work.

When I use the below formula I get the result of: 0

Please Login or Register  to view this content.

Is there a active sheet limitation to the SMALL formula only working where the actual data resides?

Thanks so much!

Try:

1.

=SMALL(INDEX((C2='Data Sheet'!A2:A50000)*'Data Sheet'!B2:B50000,),COUNTIF('Data Sheet'!A2:A50000,"<>"&C2)+1+
COUNTIFS('Data Sheet'!A2:A50000,C2,'Data Sheet'!B2:B50000,""))


2.

=SMALL(INDEX((C2='Data Sheet'!A2:A50000)*'Data Sheet'!B2:B50000,),COUNTIF('Data Sheet'!A2:A50000,"<>"&C2)+1+
SUM(COUNTIFS('Data Sheet'!A2:A50000,C2,'Data Sheet'!B2:B50000,{"",0})))

I suggest a helper column.
Say D2 put
=IF(A2=C$2,B2,"")
Fill down to D50000

Then just use
=MIN(D2:D50000)

Aside from that, it's not merely the existence of an array formula that makes a formula slow.
It's what the formula actually does that makes it slow.

I haven't tested, but I would venture this array formula is no less efficient than the original formula you posted
That original formula is an array formula BTW, it's just some clever use of other functions that make it not require CSE entry.

=MIN(IF(A2:A50000=C2,IF(B2:B50000>0,B2:B50000,"")))