Looking to return average if criteria is met, but I can't get the formula to work when one of my criteria exists in multiple columns. Does anyone have an efficient solution?
=AVERAGEIFS(Sheet2!D:D,Sheet2!A:A,A2,Sheet2!B:C,B2)
1 Answer
There are many ways to do that. JohnSUN already showed how to do it with filter(). Here's how you would do it with query():
=query(
Sheet2!A$1:D,
"select avg(D)
where A = '" & A2 & "'
and(B = '" & B2 & "' or C = '" & B2 & "')
label avg(D) '' ", 0
)
answered Dec 17, 2022 at 17:36
=AVERAGE(FILTER(D:D,(A:A=A2)*((B:B=B2)+(C:C=B2))))?