Can Averageifs work with multiple criteria in the same column range?

[The French Viking]

Hi,

I've gained a ton of help from searching the forums here, but I can't find one on this pesky problem that I've narrowed down in a large formula:

I'm using the syntax Averageifs to calculate the average months of service in 'Data"!Q:Q based on two or more criteria in column 'Data'!O:O. I want to average the months of service for those who left the company due to "Unknown" and "ReturntoSchool."

Here's the part of a large formula that is returning #DIV/0!:

=AVERAGEIFS('Data'!Q:Q,'Data'!O:O,"Unknown",'Data'!O:O,"ReturntoSchool")

Again, this is part of a large formula with other criteria, but if I use more than one criteria for the same range I get the #DIV/0! error message. Have I hit a road block?

Thanks for reading and helping!

 

[Aladin Akyurek]

Thanks for the sample.

C2, copied across to D2, and down:

=AVERAGEIFS(F$2:F$44,$E$2:$E$44,">="&$A2,$E$2:$E$44,"<="&$B2)

 

[Hanneske4]

Thank you so much! All I had to do was replace the comma's by a semicolon and it worked like a charm! This is more or less what I was trying before but in Excels instructions it looked like every second argument needed to be between [] after a semicolon and thats why it didn't work apparently. You handled it much smarter though. I inserted an extra column between A and B before containing the other value and added three arguments but in this way its easier since I can even use it if I want to calculate multiple day averages other than three day averages in the future for my data. Vriendelijk bedankt!

 

[Aladin Akyurek]

Thank you so much! All I had to do was replace the comma's by a semicolon and it worked like a charm! This is more or less what I was trying before but in Excels instructions it looked like every second argument needed to be between [] after a semicolon and thats why it didn't work apparently. You handled it much smarter though. I inserted an extra column between A and B before containing the other value and added three arguments but in this way its easier since I can even use it if I want to calculate multiple day averages other than three day averages in the future for my data. Vriendelijk bedankt!

Graag gedaan. Thanks for providing feedback.

 

[mustafaabuasab]

You need to switch to something like...

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,{"Unknown","ReturntoSchool"},0)),
     Data!Q2:Q2000))

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,{"Unknown","ReturntoSchool"},0)),
      Data!Q2:Q2000))))

The latter avoids #DIV/0! when there is no match...

I was having a similar problem.. I had to search everywhere to find the solution, until I came here and I must say "Sir you have my respect". Amazing!

 

[Aladin Akyurek]

I was having a similar problem.. I had to search everywhere to find the solution, until I came here and I must say "Sir you have my respect". Amazing!

That's great. Thanks for the kind words.