SUMPRODUCT and AVERAGE together

[aiwnjoo]

Hello, i am using this formula;

=SUMPRODUCT(--('Jun-18'!$L:$L>=$B$2), --('Jun-18'!$L:$L<=$C$2),--('Jun-18'!$L:$L<="Open"))

which returns a number of tickets closed between two date periods (dd/mm/yyyy hh:mm:ss) and works, i want to now add to this formula and of those 458 results i want to average column D in Jun-18 which has the individual hh:mm:ss for duration between the two dates.

Thanks

 

[aiwnjoo]

Managed to solve it with;

=AVERAGEIFS('Jun-18'!$J:$J,'Jun-18'!$E:$E,">="&$B2,'Jun-18'!$G:$G,"<="&$C2)

E:E is the range of From dates
G:G is the range of To dates
B2 and C2 are From and To dates in a column for a particular month (i.e. 01/06/2018 to 30/06/2018), J is the range of times I want to average.

Seems to work well, I then use custom filter to display the result as dd "Days", hh "Hours", mm "Minutes" which adds a random leading space but not to worry.

Thanks