AverageIfs for multiple criteria in different columns

**HBEE** · 02-03-2013, 11:21 PM

I am trying to find a formula that finds the average for multiple criteria in different columns (whether or not it is actually there, I still need excel to verify that it checked all columns). I tried to use this as an example:=AVERAGEIFS(K4:K2409,F4:F2409,"K2303",B4:E2409,"TN"). I also tried to break out the B, C, D, and E columns for the criteria. For example =AVERAGEIFS(K4:K2409,F4:F2409,"KT303",B4:B2409,"TN",C4:C2409,"TN",D4:D2409,"TN",E4:E2409,"TN"). Because TN can show up in either row and column combination...I need a formula that will let me get the average if TN is in the row anywhere in the 4 (B, C, D, and E columns). Any ideas?

**vlady** · 02-03-2013, 11:30 PM

try

Formula:

Please Login or Register  to view this content.

**rcm** · 02-03-2013, 11:51 PM

Averageifs just deals with and clauses .Here is a method to test OR clauses

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 02-04-2013, 06:02 AM

Try thus formula
Gives average of K4:K2409, If F column is "KT303" and Any of the columns C , D , E contains "TN".

Please Login or Register  to view this content.

**HBEE** · 02-05-2013, 12:10 PM

Thanks! I tried vlady's and it worked! Thanks all who nd helped me out!

**daddylonglegs** · 02-05-2013, 12:21 PM

This "array formula" should also work

=AVERAGE(IF((F4:F2409="KT303")*(B4:E2409="TN"),K4:K2409))

confirmed with CTRL+SHIFT+ENTER

Edit: although both that formula and Vlady's will include K4, for example, multiple times in the average if B4, C4, D4 and E4 are all = "TN". Is it possible to have multiple "TN"s on one row? If so do you want to include that row only once or multiple times? If only once change formula to

=AVERAGE(IF(MMULT((F4:F2409="KT303")*(B4:E2409="TN"),{1;1;1;1}),K4:K2409))