Search code examples
google-sheetsgoogle-sheets-formulaaverage

Getting an Error while using AverageIFS Function

I have been receiving an error while using AverageIFS function the error is Evaluation of function AVERAGEIFS caused a divide by zero error.

I am matching 4 columns where it gives error but when use for three it works fine.

=AVERAGEIFS(Sheet1!$C:$C,Sheet1!$A:$A,D3,Sheet1!$B:$B,$A$2,Sheet1!$D:$D,$B$2,Sheet1!$D:$D,$B$3)

Link to Sheet:

Solution

  • In cell G2 of Sheet 2 I entered

    =query(Sheet1!A:D, "Select A, avg(C) where B ='"&A2&"' and D matches '"&textjoin("|", true, B2:B3)&"' group by A label avg(C) 'Average'", 1)

    As an alternative for your current solution you can try

    =AVERAGE(FILTER(Sheet1!$C:$C,Sheet1!$A:$A=D3,Sheet1!$B:$B=$A$2,( (Sheet1!$D:$D=$B$2) + (Sheet1!$D:$D=$B$3))))

    and drag down.

    The current solution does not work because there are no rows where all conditions are met. E.g: Column D can't contain 'PAF' and 'PAN'.

    See if that works for you?