Go Back

How to use the excel AVERAGEIFS function

We can use the excel AVERAGEIFS to get the average of numbers in a given range which meets one or more supplied criteria. This article provides a clear guide on how to use the excel AVERAGEIFS function.

Figure 1: How to use excel AVERAGEIFS function

General syntax of the formula

=AVERAGEIFS(avg_rng, range1,criteria1,[range2], [criteria2],….)


  • Avg_rng- is the range to average
  • Range1- refers to the first range that we want to evaluate
  • Criteria1- refers to the criteria to use on range1
  • Range2- this is the second range to evaluate, and it is optional.
  • Criteria2- refers to the criteria to use on range2 and it is optional as well

Understanding the formula

  • We use the AVERAGEIFS function to average cells that meet a supplied criteria.
  • This function can be used with more than one criteria, and with more than one range.
  • The range to be averaged is the first range
  • Also, the criteria are supplied in pairs, and the first pair is the only one that is mandatory.
  • The formula can take up to 127 range/criteria pairs.
  • The formula will return a #DIV0! Error if none of the criteria are met
  • For the formula to work as required, each of the additional range must have the same number of rows and columns as the avg_range.
  • We also need to enclose non-numeric criteria in double quotes. We can leave numeric criteria without the double quotes
  • We can also use wildcards in the criteria, i.e.? And *. Here, it is important to note that the question mark can match any one character while the asterisk can match any sequence of characters.
  • For you to find a literal question mark or asterisk, you will have to use a tilde in front of the question mark or asterisk.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I am getting a DIV/0! error for some cells using AVERAGEIFS function. I have used this formula for the column the AVERAGEIFs will be using =IFERROR((V18/(V18+W18)), "") so technically any number that divides by 0 will come out as blank and therefore the AVERAGEIFS shouldn't take it into account. Please tell me what I'm doing wrong?!
Solved by D. J. in 24 mins
averageifs formula with conditions
Solved by D. Q. in 14 mins
averageifs issue getting it to pull from multiple tabs.
Solved by B. H. in 14 mins

Leave a Comment