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.