Find Date that meets multiple criteria

[S_CGoodgirl]

Hello and thank you so much for being here!
I am constantly coming to Mr Excel to learn new techniques, but this one has me stumped.

I am trying to have the oldest date listed in column F (Other than the blank ones), for rows where column K = 1 and column G = TRUE.........I always get the date 1/0/1900 listed if there are no other dates in F and even if K is 0, it is still listing the date in F that is associated with that row (when it shouldn't). BTW, the formula below does have the {} around them.

This particular formula is listed in cell F10

'=(MIN(IF(IF(IF(ISBLANK(F$12:F$640),1,0),K$12:K$640=1,0),G$12:G$640=TRUE),F$12:F$640))

I have a snippet of the cells that show the data, but I have no access to a URL and cannot post the image in here.

Any advice would be truly appreciated!!

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

=(MIN(IF(G$12:G$640=TRUE,IF(K$12:K$640=1,F$12:F$640))))

 

[S_CGoodgirl]

Yes, it does!!! and yours is way simpler than mine....will have to look at this in detail to see the logic (I can hardly see straight from trying too many things).

However, if I have no dates listed in F, it still gives me 1/0/1900. I have not found a way to eliminate listing a date that has a date value less than 1 or even 10 (I figured there was no way I would ever use that date).....I've tried <>0, x<10 and ISBLANK and none seem to work. Do you have a workaround for null dates?

Thank you soooo, so much.

 

[Andrew Poulsom]

Unfortunately MIN returns zero for an array that contains only FALSE. If you have Excel 2007 or above try:

=IF(SUMIFS(F$12:F$640,G$12:G$640,TRUE,K$12:K$640,1),MIN(IF(G$12:G$640=TRUE,IF(K$12:K$640=1,F$12:F$640))),"")

 

[S_CGoodgirl]

You are truly an MVP!
I have Excel 2010 and it works perfectly (when entered with the Ctl, Shift, Enter).

You've made my day. Thank You