Adding condition to average time within another sheet-CodePudding

Trying to add an additional condition to average time within another sheet.

This formula works currently to pull the average time within a range

'''=ARRAYFORMULA(AVERAGE(VALUE(TEXT("00"&Interaction_Data!R3:R,"hh:mm:ss"))))'''

I need to add an if condition to only average the rows that also contain "Partner 1" within the range C3:C of the "Interaction_Data" sheet

I have tried this following formula as I thought this would do it but it returns error #N/A '''=ArrayFormula(if(AVERAGE(VALUE(TEXT("00"&Interaction_Data!R3:R,"hh:mm:ss")),Interaction_Data!C3:C,"partner 1")))'''

I feel like I have tried all other positions for the If statement, range and criteria but I have obviously missed the one that will work.

Any help will be awesome please

CodePudding user response：

AVERAGEIF does not work with ARRAYFORMULA. Use QUERY instead.

=ARRAYFORMULA(QUERY(
{VALUE(TEXT("00"&Interaction_Data!R3:R,"hh:mm:ss")),Interaction_Data!C3:C},
"select avg(Col1) where Col2='partner 1' label avg(Col1) ''"))