How to use the MAXIFS Function in Google Sheets
Returns the maximum value in a range of cells, filtered by a set of criteria.
Google Sheets offers a range of powerful functions to handle data analysis and reporting. One such function is MAXIFS, which is designed to return the maximum value from a range of cells that meet one or more specified conditions. This function is particularly useful for filtering large datasets and extracting meaningful insights based on specific criteria.
??Purchase our book to improve your Excel productivity
Benefits
1. Efficiency: Automates the process of finding the maximum value based on multiple conditions, saving time and reducing the risk of manual errors.
2. Flexibility: Allows the application of multiple criteria, making it adaptable to various data analysis scenarios.
3. Accuracy: Ensures precise results by only considering cells that meet all specified conditions.
4. Data Analysis: Enhances the ability to perform complex data analyses and generate reports directly within Google Sheets.
Step-by-Step Guide
Syntax
```plaintext
MAXIFS(max_range, criteria_range1, criterion1, [criteria_range2, criterion2], …)
- max_range: The range of cells from which you want to find the maximum value.
- criteria_range1: The range of cells to be evaluated by criterion1.
- criterion1: The condition that specifies which cells should be included in the calculation.
- [criteria_range2, criterion2]: Optional additional ranges and criteria.
??Purchase our book to improve your Excel productivity
Scenario
Suppose you have a sales dataset, and you want to find the maximum sales amount for a specific product in a particular region.
Sample Data
| Product | Region | Sales |
|---------|--------|-------|
| A | North | 150 |
| B | South | 200 |
| A | East | 100 |
| B | West | 250 |
| A | North | 300 |
| B | South | 400 |
| A | East | 350 |
Step-by-Step Example
1. Open Google Sheets: Start by opening your Google Sheets document.
2. Input Data: Enter the sample data into your sheet.
| A | B | C |
|---------|--------|-------|
| Product | Region | Sales |
| A | North | 150 |
| B | South | 200 |
| A | East | 100 |
| B | West | 250 |
| A | North | 300 |
| B | South | 400 |
| A | East | 350 |
3. Apply MAXIFS Function: In an empty cell, enter the following formula to find the maximum sales for product A in the North region.
=MAXIFS(C2:C8, A2:A8, "A", B2:B8, "North")
4. Review Result: The formula will return 300, which is the maximum sales amount for product A in the North region.
??Purchase our book to improve your Excel productivity
Example
Let’s expand the scenario to find the maximum sales for product A in any region but only considering sales greater than 100.
1. Input Additional Data: Modify the data if necessary to ensure a comprehensive example.
领英推荐
| A | B | C |
|---------|--------|-------|
| Product | Region | Sales |
| A | North | 150 |
| B | South | 200 |
| A | East | 100 |
| B | West | 250 |
| A | North | 300 |
| B | South | 400 |
| A | East | 350 |
2. Apply MAXIFS Function with Multiple Criteria: Use the following formula to find the maximum sales for product A where sales are greater than 100.
=MAXIFS(C2:C8, A2:A8, "A", C2:C8, ">100")
3. Review Result: The formula will return 350, which is the maximum sales amount for product A with sales greater than 100.
??Purchase our book to improve your Excel productivity
Advanced Tips
1. Using Wildcards: Wildcards like * (any number of characters) and ? (single character) can be used in criteria for text matching.
=MAXIFS(C2:C8, A2:A8, "A*", B2:B8, "North")
2. Dynamic Criteria: Use cell references instead of hard-coded values for criteria to make the function more dynamic and adaptable.
=MAXIFS(C2:C8, A2:A8, E1, B2:B8, F1)
Where E1 contains the product and F1 contains the region.
3. Combining with Other Functions: Combine MAXIFS with other functions like ARRAYFORMULA, IF, or FILTER for more complex scenarios.
=ARRAYFORMULA(MAXIFS(C2:C8, A2:A8, "A", B2:B8, {"North", "East"}))
4. Handling Errors: Use IFERROR to handle cases where no data meets the criteria, preventing errors in your sheet.
=IFERROR(MAXIFS(C2:C8, A2:A8, "A", B2:B8, "Non-Existent Region"), "No Data")
??Purchase our book to improve your Excel productivity :
??102 Most Useful Excel Functions with Examples: The Ultimate Guide
???? Order it here : https://lnkd.in/enmdA8hq
?? Transform from novice to pro with:
?? Step-by-Step Guides
??? Clear Screenshots
?? Real-World Examples
?? Downloadable Practice Workbooks
?? Advanced Tips
Construction Manager - Algarve Builders
8 个月@ @ M.Alexandre Monte