In the realm of spreadsheet functionalities, Excel stands out as a powerhouse for data analysis and manipulation. Among its arsenal of functions, the AVERAGEIFS function emerges as a versatile tool, allowing users to calculate averages based on specific criteria within a range of cells. In this comprehensive guide, we will delve into the intricacies of the AVERAGEIFS function, exploring its syntax, supported versions, and providing practical examples to illuminate its usage. Additionally, we will navigate through essential tips and potential issues, ensuring a comprehensive understanding of this indispensable Excel feature.
Supported versions
- (Windows) Excel 2007 and above
- (Mac) Excel for Mac 2011 and above
- Web and mobile versions
AVERAGIFS Syntax
Arguments
| average_range | The range of cells to sum |
| criteria_range1 | The range of cells that you want to apply the criteria1 against |
| criteria1 | The criteria that is applied to criteria_range1 to define which cells to calculate |
| [criteria_range2, criteria2] | Optional. Additional ranges and their associated criteria pairs. You can enter up to 127 range/criteria pairs. |
Examples
Here, we used named ranges to make the formulas easier to read. This is not required.
Example 1
Note: SUMIFS function is not case-sensitive. Therefore, "WATER" and "water" criteria will work the same way.
Example 2
Tips
- Use the same number of rows and columns for the sum and criteria range arguments.
- Bad Example: =AVERAGIFS(G2:G15,F2:H10,">2014",J2:J20,"IT")
- Good Example: =AVERAGIFS(G2:G11,F2:F11,">2014",J2:J11,"IT")
- Comparison operators:
| Operator | Description | Criteria Sample | Criteria Meaning |
| = | Equal to | “=10000” | Equal to 10000 |
| <> | Not equal to | “<>10000” | Not equal to 10000 |
| > | Greater than | “>10000” | Greater than 10000 |
| < | Less than | “>10000” | Less than 10000 |
| >= | Greater than or equal to | “>=10000” | Greater than or equal to 10000 |
| <= | Less than or equal to | “<=10000” | Less than or equal to 10000 |
| ? | Takes the place of a single character | “Admin?” | 6-character word starts by “Admin” |
| * | Can take the place of any number of characters. | “Admin*” | Any number of character word starts with “Admin” |
| ~ | Use tilde in front of a question mark or an asterisk to actually find them | “Admin~*” | Equal to "Admin*" |
Note: Wildcards cannot be used for numeric values. Searching with a wildcard in a range of numeric values returns no matches.
Issues
#VALUE!
The SUMIFS function returns incorrect results when you use it to match strings longer than 255 characters, or the string #VALUE!.
TRUE and FALSE
TRUE and FALSE values in average_range are evaluated as numbers. While TRUE is evaluated as 1, FALSE is evaluated as 0. As a result, this condition may give unexpected results when they are used in calculations with other values.
Empty Cell Reference
If criteria argument refers an empty cell, the AVERAGEIFS function evaluates the cell value as 0.
In conclusion, mastering the AVERAGEIFS function in Excel opens a gateway to precision in data analysis. We have navigated through the syntax, examples, and nuances of this function, shedding light on its capabilities and intricacies. Armed with the ability to calculate averages based on specified criteria, Excel users can enhance their data processing skills, making informed decisions and deriving valuable insights. As you embark on your Excel journey, harness the power of AVERAGEIFS to unlock the full potential of your data analysis endeavors. Excel proficiency is not just about formulas; it's about leveraging the right tools at the right time, and AVERAGEIFS is undoubtedly a key player in this strategic data manipulation game.
