Return the earliest result and latest result with criteria

**PFDave** · 04-25-2016, 08:58 AM

Hi,

I believe what I'm asking for requires a MINIFS/MAXIFS however I do not have the functions in the version 2013 I'm using.

What I have is a dump of data with numerous columns where I need to find out when a user starts and finishes their daily maintenance. This is determined by matching their asset in column A, by matching the task they are doing in column H, and it should be between two times, the start in P and the end in Q.

They may do other tasks in between so I need to find when the earliest start time is between the window, and when the last end time is between the window.

The criteria I need to find is:

A:A=A2
H:H="*Word*" (anywhere in the text in column H)
C:C">="&P2
D:D"<="&Q2

I've been able to provide some results based on two criteria using MIN+IF but I'm struggling to make it all work.

Thanks in advance

**JeteMc** · 04-25-2016, 09:18 AM

Please clarify. Your post says that you are using Excel 2013, however your user info says you are using Excel 2003.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

**PFDave** · 04-25-2016, 09:34 AM

Hi,

I've attached an example sheet now.

I'm also unable to update my MS version as My Profile won't load where I need it too but I am conscious of this.

Thanks

**FDibbins** · 04-25-2016, 10:01 AM

MAXIFS etc does not yet exist.

Try this ARRAY formula...
=SMALL(IF($A$2:$A$11=A2,$C$2:$C$11),1)
...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.

Change that to LARGE and adjust the range for the latest

**AlKey** · 04-25-2016, 10:09 AM

Originally Posted by FDibbins

MAXIFS etc does not yet exist.

Try this ARRAY formula...
=SMALL(IF($A$2:$A$11=A2,$C$2:$C$11),1)
...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.

Change that to LARGE and adjust the range for the latest

Actually it does exist but only in Excel 2016 version. However, if you use Excel 2010 or higher you can use AGGREGATE function.
Enter as a regular formula

Formula:

Please Login or Register  to view this content.

**PFDave** · 04-25-2016, 10:13 AM

Ford, AlKey; both your functions only look at one criteria and as stated in the OP I can work this out using MIN(IF( but just not for the volume of criteria, unless I'm making a mistake in that function

**AlKey** · 04-25-2016, 10:34 AM

You may need to review this:

A:A=A2
H:H="*Word*" (anywhere in the text in column H)
C:C">="&P2
D:D"<="&Q2

Your Col H does not contain any records with text word.
Col C contains Date and Time and therefor can't match Col P
Col D contains Date and Time and therefor can't match Col Q

Here is an adjusted formula. I used word 'Task' instead of 'word'

Formula:

Please Login or Register  to view this content.

**PFDave** · 04-25-2016, 10:38 AM

Your Col H does not contain any records with text word. - contain Maintenance
Col C contains Date and Time and therefor can't match Col P - K is time for P now
Col D contains Date and Time and therefor can't match Col Q - M is time for Q now

Because I've split the time out, I need one more condition which is that date only look for those on the same date as J2, so J:J=J2

Thanks AlKey

**FDibbins** · 04-25-2016, 10:42 AM

Originally Posted by AlKey

Actually it does exist but only in Excel 2016 version. However, if you use Excel 2010 or higher you can use AGGREGATE function.
Enter as a regular formula

Formula:

Please Login or Register  to view this content.

I am using 2016 Pro plus, and it does not have them?

**FDibbins** · 04-25-2016, 10:43 AM

Originally Posted by PFDave

Ford, AlKey; both your functions only look at one criteria and as stated in the OP I can work this out using MIN(IF( but just not for the volume of criteria, unless I'm making a mistake in that function

That formula was built around your sample file. If that was not what you needed, then tell me more?

**AlKey** · 04-25-2016, 10:46 AM

Originally Posted by FDibbins

I am using 2016 Pro plus, and it does not have them?

I guess, you need to go to File, Account and click on Office update. There are new functions that were added in January.
http://www.excelforum.com/showthread.php?t=1128056

**JeteMc** · 04-25-2016, 10:51 AM

I've got two array formulas* for you to look at in the attached file. The first one goes in column R and returns the same results that you have in the example:

Please Login or Register  to view this content.

The second on goes in column S and returns most of the results you have in the example:

Please Login or Register  to view this content.

I am not clear on why the example has Dave's end time as 4/6/2016 9:51:00 AM since that is later than the value in column Q which is 9:15 so 8:50 is the time yielded by the second formula.
*Array formulas are 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.
Let me know if you have any questions

**FDibbins** · 04-25-2016, 10:53 AM

hmm my updates are set to automatic, and when I tried to manually update, it saw i was already up to date

**PFDave** · 04-25-2016, 10:56 AM

Originally Posted by FDibbins

That formula was built around your sample file. If that was not what you needed, then tell me more?

Hi,

Ok I understand I should and could have been clearer.

So the overview is that I have thousands of rows where I'm looking to establish how much time an individual on a certain day spent doing maintenance.

So I need the formula to return the smallest result of this criteria

For lowest

A:A must match A2
J:J must match J2
H:H must contain Maintenance
K:K must be greater or equal than P2 AND less than or equal Q2

I need this to return the lowest remaining value from column K
For highest

A:A must match A2
J:J must match J2
H:H must contain Maintenance
K:K must be greater than P2 AND less than Q2

I need this to return the highest remaining value from column M

**PFDave** · 04-25-2016, 10:59 AM

Originally Posted by JeteMc

I am not clear on why the example has Dave's end time as 4/6/2016 9:51:00 AM since that is later than the value in column Q which is 9:15 so 8:50 is the time yielded by the second formula.

The end time can be after the advised end time in Q providing the start time is between the start and end.

So basically Dave went into maintenance at 8:51 which is during the time window, but for whatever reason he over ran the usual time period which is exactly what I want to see, so I need the 9:51 to be returned.

If they start early then I do care in some ways but for the purpose of this I don't.

Thanks

**PFDave** · 04-25-2016, 11:03 AM

Originally Posted by JeteMc

I've got two array formulas* for you to look at in the attached file. The first one goes in column R and returns the same results that you have in the example:

Please Login or Register  to view this content.

The second on goes in column S and returns most of the results you have in the example:

Please Login or Register  to view this content.

Let me know if you have any questions

This is making progress, just misses off the condition for date which I didn't specify in the OP so that's my bad. But it also needs to look at the start times condition as mentioned above for the end time. I'm going to have a play about with this now and see if I can touch it up.

Thanks you making real headway here

**JeteMc** · 04-25-2016, 11:19 AM

Modify the formula for column S to read:

Please Login or Register  to view this content.

Let me know if you have any questions.

**PFDave** · 04-25-2016, 11:24 AM

Originally Posted by JeteMc

Modify the formula for column S to read:

Please Login or Register  to view this content.

Let me know if you have any questions.

JeteMc it's almost there!

Just need it to also have adding it that the date is considered as I have a few weeks' worth of data but I'm struggling to adjust the formula to do this.

**JeteMc** · 04-25-2016, 11:49 AM

To included date checking try this array formula* in column R:

Please Login or Register  to view this content.

Try this one in column S:

Please Login or Register  to view this content.

Let me know if you have any questions.

**PFDave** · 04-26-2016, 04:05 AM

Thanks JeteMc, looks great now. I do get #NUM! when there is no value which hits the criteria so I'll now add something to the front of this to change the value on that. thanks again

**JeteMc** · 04-26-2016, 07:20 AM

You're Welcome and thank you for the feedback. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

Return the earliest result and latest result with criteria

LinkBack

Thread Tools

Rate This Thread

Display

Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Re: Return the earliest result and latest result with criteria

Thread Information

Users Browsing this Thread

Similar Threads

How to find the earliest or latest date with criteria selection?

[SOLVED] Formula to return earliest and latest time.

Find the earliest result for the same period

Lookup two criteria and return a third criteria as result. Aaaargh!

Return result from two criteria

excel result return wrong calcuation result

Advanced formula - Return result & Show Cell Reference of result

Bookmarks

Bookmarks

Posting Permissions