The `MAXIFS` function in Excel is used to find the maximum value in a range based on one or more criteria. It is particularly useful when you need to analyze large datasets and want to determine the maximum value that meets certain conditions. Here is a step-by-step guide on how to use the `MAXIFS` function:
Syntax
MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- max_range: The range of cells from which you want to find the maximum value.
- criteria_range1: The range of cells to be evaluated with the criteria.
- criteria1: The condition or criteria to evaluate within `criteria_range1`.
- [criteria_range2, criteria2], …: Additional pairs of ranges and criteria, if more than one condition needs to be applied.
Example
Suppose you have a dataset with sales data in columns A (Product), B (Salesperson), C (Sales), and you wish to find the maximum sales made by a specific salesperson for a specific product.
| A | B | C |
|———-|————|—–|
| Product | Salesperson| Sales|
| Laptop | Alice | 1000|
| Laptop | Bob | 1200|
| Phone | Alice | 800 |
| Phone | Bob | 950 |
To find the maximum sales by Alice for “Phone”, you can use the `MAXIFS` function as follows:
=MAXIFS(C2:C5, A2:A5, "Phone", B2:B5, "Alice")
Instructions
- Select the Cell: Click on the cell where you want the result of your `MAXIFS` function to appear.
- Enter the Function: Type `=MAXIFS(`.
- Specify max_range: Select the range where the maximum value is to be found, e.g., `C2:C5`.
- Select Criteria Range and Set Criteria:
- For the first condition (e.g., the product is “Phone”), provide the range `A2:A5` and the criteria “Phone”.
- For the second condition (e.g., the salesperson is “Alice”), provide the range `B2:B5` and the criteria “Alice”.
- Close the Function and Press Enter: Type the closing parenthesis `)` and hit Enter. The cell should now display the maximum sales value by Alice for phones, which in this case would be 800.
Tips
- Ensure that all criteria ranges are of the same size as `max_range`, as mismatched sizes will yield an error.
- You can use wildcards (`*` and `?`) in the criteria for pattern matching.
- Logical operators (`>`, `<`, `=`, `<>`) can be used to create conditions (e.g., `”>1000″`).
By following these steps, you can efficiently use the `MAXIFS` function to analyze your data based on multiple conditions.
Unlock Your Potential
Excel
Access
Power BI
