find longest duration between 2 dates based on specic criteria

[learning1984]

Hello,

this formula works great, (=MAX(TODAY()-MAX(Log!$D$5:$D$482),AGGREGATE(14,4,Log!$D$6:$D$482-Log!$D$5:$D$481,1))-1)) to find the longest duration between all incidents

i'm just wondering if I can add an "if" to the beginning of the formula,

i am wanting to find the longest duration between incidents that occurred in section 1, team 1, section 1 team 2 etc

the formula I have come up with so far just results in false.

=IF(Log!$EU$5:$EU$499=AC18,IF(Log!$I$5:$I$499=AC17,MAX(TODAY()-MAX(Log!$D$5:$D$482),AGGREGATE(14,4,Log!$D$6:$D$482-Log!$D$5:$D$481,1))-1))

in the "log" sheet, column EU contains the Team number, column I is the section number

this is both with and with out ****, Ctrl, Enter.

example of basic sheet, without the section and team columns in view

[TABLE="class: cms_table, width: 372"]
<tbody>[TR]
[TD]M.I Ref[/TD]
[TD]Investigator[/TD]
[TD]Date[/TD]
[TD]Period[/TD]
[/TR]
[TR]
[TD]-[/TD]
[TD]team 1[/TD]
[TD]25/09/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2609161934[/TD]
[TD]team 1[/TD]
[TD]26/09/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2609161640[/TD]
[TD]team 7[/TD]
[TD]26/09/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]team 4[/TD]
[TD]27/09/2016[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]team 1[/TD]
[TD]01/10/2016[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]