Help with MaxIfs and MinIfs

[matthec2]

Hi Guys, little help much appreciated

I'm trying to do a Maximum query on a set of data with multiple criteria and then the same Minimum Query

Basically i'm looking at our company phone stats to track when the sales team have logged in and out the the phome system.

At first i was doing a simple Sumif which worked well for 90% of the days and concierge.

Then i realised that certain days there were multiple lines of data created when the sales person may have accidentally logged out the system then straight back in, so this created another two lines of data on that date for that sales person.

i tried to add in a column to create "unique log" but this required the original datafeed to come through in a specific order which i can't guarantee every time.

I know I need to use Minimum and Maximums and Array Formulas based on 3 criteria.

So basically the time the agent logged in each day would be:
Minimum TIME (4th Column) - 1st Criteria DATE (5th Column) - 2nd Criteria EXPLANATION (3rd Column s/b AMBLogin) - 3rd Criteria AGENTID (1st Column)

And Log out time would be:
Maximum TIME (4th Column) - 1st Criteria DATE (5th Column) - 2nd Criteria EXPLANATION (3rd Column s/b AMBLogout) - 3rd Criteria AGENTID (1st Column)

See table below (sorry it isn't embedded very well)

[TABLE="width: 479"]
<tbody>[TR]
[TD] AgentId[/TD]
[TD] DateTime[/TD]
[TD] Explanation[/TD]
[TD]TIME[/TD]
[TD]DATE[/TD]
[TD]UNIQUE LOG[/TD]
[/TR]
[TR]
[TD]1272[/TD]
[TD]13/05/2014 17:14[/TD]
[TD]AMBLogout[/TD]
[TD]5:14 PM[/TD]
[TD]13/05/2014[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]1272[/TD]
[TD]13/05/2014 17:23[/TD]
[TD]AMBLogout[/TD]
[TD]5:23 PM[/TD]
[TD]13/05/2014[/TD]
[TD]Last Logout[/TD]
[/TR]
[TR]
[TD]1272[/TD]
[TD]13/05/2014 17:23[/TD]
[TD]AMBLogin[/TD]
[TD]5:23 PM[/TD]
[TD]13/05/2014[/TD]
[TD]1st Login[/TD]
[/TR]
[TR]
[TD]1272[/TD]
[TD]13/05/2014 09:15[/TD]
[TD]AMBLogin[/TD]
[TD]9:15 AM[/TD]
[TD]13/05/2014[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

So looking at the example above, Log in time would be 9:15 AM and log out time would be 5:23 PM

Hope that makes sense

many thanks!

 

[Fazza]

If there are a few to do, what about using pivot tables?

ALT-D-P & follow the wizard. For minima, set page field Explantion to "AMBLogin", row fields for AgentID & DateTime, data field MIN of TIME. For maxima adjust as required - that is, Explanation filter & MAX of TIME

HTH