# Google Sheets AVERAGEIF returning DIV/0 error for nonzero data

I'm working on a formula to average the duration of several matches in a tournament. Specifically, I'm trying to find the average duration for specific pairings, which has led me to the following formula:

=AVERAGEIF(C2:E,AND(C2="Alice",E2="Bob"),AC2:AC)

The names of the competitors are in columns C and E, hence C2:E for the Criterion Range.

The AND function is looking to count only the matches where Alice and Bob are competing (Alice is always listed before Bob in their matches, so this catches all of them). I could generalise this to look at cells AE2 and AF2 (where Alice and Bob are listed again, to show that the following data is their average match length), but I'm using strings in this example for legibility.

Lastly, AC2:AC covers all the match lengths, and so this is the range I want to average. These are generally between 10 and 60 minutes, but some last more than an hour. They are all nonzero.

I have checked that all entries in column AC are formatted as a duration - however, they are all calculated by a formula (summing the length of the best-2-of-3 rounds for the total match length), so the snag might potentially be there somehow.

This all seems correct to me, and sure enough, a simple AVERAGE(AC2:AC) will give me the average time for all the matches, since there aren't any problems with the data. However, putting it all together in the AVERAGEIF formula returns a DIV/0 error, and I cannot fathom why. What's wrong with the way I've made this formula? I initially thought it might have been some sort of parsing issue with trying to average a duration, but the regular average handles it just fine, so I'm at a loss for what the mistake is here.

• Welcome! May be `=AVERAGEIFS(AC2:AC, C2:C,"Alice", E2:E,"Bob")` or `=AVERAGEIFS(AC2:AC, C2:C,\$AE\$2, E2:E,\$AF\$2)`? Aug 31, 2022 at 15:09

Try the following

``````=INDEX(AVERAGEIF(C2:C&E2:E,"="&C2&E2,F2:F))
``````

• Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. Aug 31, 2022 at 15:11