Average Ifs with multiple criteria

Occasional Contributor

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! 

 

4 Replies

@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))

@Hans Vogelaar
Unfortunately not It returns a div/0 error, which is not possible since the value shouldent be 0

@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.

@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.

dscheikey_0-1663083110237.png

 

averageifs-function