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

M.I Ref	Investigator	Date	Period
-	team 1	25/09/2016	1
2609161934	team 1	26/09/2016	1
2609161640	team 7	26/09/2016	1
	team 4	27/09/2016	1
	team 1	01/10/2016	1

<tbody>
</tbody>