# Return the earliest result and latest result with criteria

1. ## Return the earliest result and latest result with criteria

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.

2. ## Re: Return the earliest result and latest result with criteria

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).

3. ## Re: Return the earliest result and latest result with criteria

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

4. ## Re: Return the earliest result and latest result with criteria

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

5. ## Re: Return the earliest result and latest result with criteria

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.`

6. ## Re: Return the earliest result and latest result with criteria

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

7. ## Re: Return the earliest result and latest result with criteria

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

Formula:
`Please Login or Register  to view this content.`

8. ## Re: Return the earliest result and latest result with criteria

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

9. ## Re: Return the earliest result and latest result with criteria

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?

10. ## Re: Return the earliest result and latest result with criteria

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?

11. ## Re: Return the earliest result and latest result with criteria

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.

12. ## Re: Return the earliest result and latest result with criteria

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

13. ## Re: Return the earliest result and latest result with criteria

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

14. ## Re: Return the earliest result and latest result with criteria

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

15. ## Re: Return the earliest result and latest result with criteria

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

16. ## Re: Return the earliest result and latest result with criteria

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

17. ## Re: Return the earliest result and latest result with criteria

Modify the formula for column S to read:
``Please Login or Register  to view this content.``
Let me know if you have any questions.

18. ## Re: Return the earliest result and latest result with criteria

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.

19. ## Re: Return the earliest result and latest result with criteria

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.

20. ## Re: Return the earliest result and latest result with criteria

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

21. ## Re: Return the earliest result and latest result with criteria

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1