Forum Discussion

WishIWerentAN00b's avatar
WishIWerentAN00b
Copper Contributor
Sep 13, 2022

Average Ifs with multiple criteria

Hey gus!

 

I am trying to find the average cost for each category under specific criteria. 

Inf_Sales_Data!B:B is the month and this should match the current month on the sheet. 

Inf_Sales_Data!AB:AB is the different categories I am looking at that are listed on a table on my sheet from A89 onwards. 

Inf_Sales_Data!T:T is the categories that include normal 1,2,3,4 and 5

Inf_Sales_Data!J:J is the cost that should be averaged. 

 

See my formula below: 

=AVERAGE(IFS((Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:$A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 2";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 3";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="short";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 4";Inf_Sales_Data!J:J)+(Inf_Sales_Data!A:A=$B$1;Inf_Sales_Data!B:B=$B$2;Inf_Sales_Data!AB:AB=A89;Inf_Sales_Data!T:T="normal 5";Inf_Sales_Data!J:J)))

 

Not only does my formula return an error, but there must be a better way to do this!

I thought about listing the categories normal 1,2,3,4 and 5 in a list as {"nomal";"nomal 1";"nomal 2";"nomal 3";"nomal 4";"nomal 5"}. 

 

(Also please note that I am using European settings on google sheets, therefore I use the ; instead of a comma) 

 

I am open to any tips, thanks so much! 

 

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    WishIWerentAN00b

    I have taken over the creation of a sample file. At least as I have understood it.

    The mean value is calculated with AVERAGEIFS. This function has been available since Excel 2007, so it should be suitable for everyone. It is also available in Google Sheets with the same syntax.

     

    averageifs-function 

     

  • WishIWerentAN00b 

    Perhaps

     

    =AVERAGE(IF((Inf_Sales_Data!$A$2:$A$1000=$B$1)*(Inf_Sales_Data!$B$2:$B$1000=$B$2)*(Inf_Sales_Data!$AB$2:$AB$1000=A89)*((Inf_Sales_Data!$T$2:$T$1000="normal")+(Inf_Sales_Data!$T$2:$T$1000="normal 1")+(Inf_Sales_Data!$T$2:$T$1000="normal 2")+(Inf_Sales_Data!$T$2:$T$1000="normal 3")+(Inf_Sales_Data!$T$2:$T$1000="normal 4")+(Inf_Sales_Data!$T$2:$T$1000="normal 5"));Inf_Sales_Data!$J$2:$J$1000))

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        WishIWerentAN00b 

        Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

Resources