Formula to pull the MIN date from a range, based on the criteria.

**Budhdr** · 01-11-2018, 09:32 AM

New user here and not sure how to go about this.
I have 2 sheets so far (plan on using more sheets but need to figure out this formula first) and I'm trying to get the MIN date based on the IF criteria. So my formula is this,
{=MIN(IF(Detailed!$E$3:$E$279=E4,Detailed!$S$3:$S$279,0))}

In my first cell it works perfectly fine, then I just drag it over and change MIN to MAX and it works fine again. But soon as I drag it down, the only value that changes is E4 to E5 to E6 and so on, which is what I want it to do. But, I get 1/0/1900, which is 0 in date form.

I'm trying to figure out why "Detailed$E$3:$E$279" only shoots out elevator? Should there be a different formula for when I wanted a range to equal a cell?

**Special-K** · 01-11-2018, 10:00 AM

Category!L4 formula is spelt wrong. You have "Deatiled" instead of "Detailed"

**JohnTopley** · 01-11-2018, 10:00 AM

Formulas should entered with Ctrl+Shift+Enter

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

They are not on posted w/book.

Enter FIRST formula as above THEN drag down

**modytrane** · 01-11-2018, 10:04 AM

I believe the problem is that the formulas aren't entered as Array.
You need to enter first set of formulas in S3 and T3 with ctrl+shft+ent. Then drag them down. I tried it and it works.

**Budhdr** · 01-11-2018, 10:04 AM

Originally Posted by Special-K

Category!L4 formula is spelt wrong. You have "Deatiled" instead of "Detailed"

I apologize, that wasn't suppose to be there I was just testing something. I should have been more specific. I am trying to get the formulas to work in the S and T column on the "Category" Sheet.

**Budhdr** · 01-11-2018, 10:06 AM

Originally Posted by JohnTopley

Formulas should entered with Ctrl+Shift+Enter

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

They are not on posted w/book.

Enter FIRST formula as above THEN drag down

I did that and I'm getting back 1/0/1900

**Budhdr** · 01-11-2018, 10:09 AM

Originally Posted by modytrane

I believe the problem is that the formulas aren't entered as Array.
You need to enter first set of formulas in S3 and T3 with ctrl+shft+ent. Then drag them down. I tried it and it works.

I rentered the formula, then click control + shift + enter but now it returned to 1/0/1900. Same exact formula.

**modytrane** · 01-11-2018, 10:20 AM

see attached file.

**Budhdr** · 01-11-2018, 10:22 AM

@modytrane, @johntopley, @speacial-k

I figure out my mistake I think, and it was working fine up until row 46 - 70 and then randomly doesn't work. I checked the spelling and it's fine. This is not working in the rows as stated before, in column S & T.

**Budhdr** · 01-11-2018, 10:28 AM

Originally Posted by modytrane

see attached file.

I appreciate that!

But why is some of my dates returning back as 1/0/1900 on my spreadsheet and not yours?

Mine: {=MAX(IF(Detailed!$E$3:$E$279=Category!E3,Detailed!$T$3:$T$279))}
Yours: {=MAX(IF(Detailed!$E$3:$E$279=Category!E3,Detailed!$T$3:$T$279))}

Mine: {=MIN(IF(Detailed!$E$3:$E$279=Category!E3,Detailed!$S$3:$S$279))}
Yours: {=MIN(IF(Detailed!$E$3:$E$759=Category!E3,Detailed!$S$3:$S$759))}

**modytrane** · 01-11-2018, 10:30 AM

See the attached file. I noticed formula in column T was wrong. I corrected it.

**Special-K** · 01-11-2018, 10:31 AM

You have blanks in Detailed!S518 and Detailed!S759
Put a date in and this fixes the problem.

**modytrane** · 01-11-2018, 10:33 AM

can you post your file that shows 1/0/1900?

**Budhdr** · 01-11-2018, 10:44 AM

Originally Posted by JohnTopley

Formulas should entered with Ctrl+Shift+Enter

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

They are not on posted w/book.

Enter FIRST formula as above THEN drag down

Originally Posted by modytrane

can you post your file that shows 1/0/1900?

Originally Posted by Special-K

You have blanks in Detailed!S518 and Detailed!S759
Put a date in and this fixes the problem.

Welp. This is very embarrassing. I overlooked the numbers and didn't realize I I inputted 279 instead of 759. Very sorry about that. Once I fixed that, made it an array, and used your formulas. All is well! Thank you very much this will make my life much easier. Didn't know how crucial arrays will be. +Rep for all!

Formula to pull the MIN date from a range, based on the criteria.

LinkBack

Thread Tools

Rate This Thread

Display

Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Re: Formula to pull the MIN date from a range, based on the criteria.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] IF/AND formula issue-Assiging date within range based on 1 criteria

Formula to pull data from one sheet to a new sheet based on date range

Formula to return values based on criteria and date range

Formula for copying a entire row based on a date range criteria.

Pull unique data within a date range that matches criteria...confused.

Based on date, pull sum from a range of cells?

Pull Data based on a date criteria

Bookmarks

Bookmarks

Posting Permissions