If you've reached this point, you likely have some understanding of the individual MAX and IF functions in Excel. When these two functions join forces, we get the Excel MAXIFS function. In this article, you'll gain a solid understanding of how to effortlessly wield the power of the Excel MAXIFS function.
What is MAXIFS Function in Excel?
Understanding the Excel MAXIFS function becomes straightforward once you become familiar with it. In Excel, the MAX function is your tool to extract the highest value from a dataset. But, with the help of Excel MAXIFS function users can get the largest numeric value from cells meeting several specific conditions, also known as criteria.
When defining these conditions, MAXIFS conveniently accommodates logical operators such as greater than (>), less than (), and equal to (=). To understand the Excel MAXIFS function, it is important to understand its syntax.
Syntax:
=MAXIFS(max_range, criteria_range1, criteria1,...)
When we enter the MAXIFS function in Excel, there are 3 compulsory arguments that we need to provide.
Max_range: The Max Range is simply a range of cells from which the Max Value is needed. Excel will simply go through the specified range, find the maximum value in this range and return the results.
Criteria_range1: It is the range of cells that Excel uses to evaluate the criteria specified by the user.
Criteria1: The criteria that will be used to assess the max value in the max_range. The criteria can be specified in the form of text, number, or cell reference.
range2, criteria2, …: These are additional ranges and their associated criteria. Up to 126 additional range/criteria pairs can be specified.
With these three arguments, Excel MAXIFS returns the maximum number in max_range where corresponding cells in range1 meet the condition set by criteria1.
Step-by-Step: How to Use the MAXIFS Function?
The Excel MAXIFS function can help users find the maximum value in a range that meets one or more conditions. It is useful in many real-world situations. In this section, we will show you how to use the MAXIFS function in Excel with a single condition or with multiple conditions.
With a Single Condition
Here's a simple example dataset demonstrating the use of the Excel MAXIFS function:
Our goal is to determine the maximum sales for each category: Clothing, Shoes, and Eyewear, using the Excel MAXIFS function.
Step 1: The first step is to call the MAXIFS function, click on an empty cell and enter the MAXIFS function: “=MAXIFS(“. Remember to use the Equals sign.
Step 2: Next we will be selecting the max_range for our function. In our example, we will be selecting the “Sales” column as our range.
Step 3: Choose criteria_range for the MAXIFS function. We have selected our “Product” column as our criteria range.
Step 4: Now, all we have to do is specify a criteria to execute the Excel MAXIFS function. We will enter “Clothing” as our criteria to find out the Maximum sales in Clothing.
Step 5: All we have to do is hit “Enter” to execute the MAXIFS function and get the results.
Step 6: Repeat the same steps to find the maximum sales for Shoes and Eyewear. Make sure to change the criteria argument to “Shoes” and “Eyewear”.
General Guidelines:
Excel MAXIFS function is not case-sensitive, therefore, even if we enter “EyeWearR” instead of “Eyewear” the function will generate accurate results.
When using text in Criteria Argument, make sure to use quotation marks to prevent Errors.
The MAXIFS function is accessible in Excel 2019, Excel for Office 365, and Excel Web. For those seeking a cost-effective solution, consider utilizing the free Excel alternative—WPS Office.
With Multiple Conditions
You can also use the Excel MAXIFS function to calculate the maximum values in a specific range based on several criteria. Let’s see an example of how to use the MAXIFS function in Excel with multiple conditions.
Let’s look at an example dataset where we have the sales values, categories, and subcategories of some products
Suppose we want to find the maximum sales value for Electronics products that have a sales value less than 1700. We can use the Excel MAXIFS function to achieve this.
Step 1: To start the MAXIFS function, we will click on an empty cell, and enter the function “=MAXIFS(“.
Step 2: Next, we will select the Sales column as our max_range. This is the range of values that we want to find the maximum from.
Step 3: For our first criteria_range, we will select the “Category” column. This is the range of cells that we want to apply our first condition to.
Step 4: Set the first criteria as “Electronics”. This means we only want to consider the cells that have “Electronics” as their category.
Step 5: Now, for our second criteria_range, we will select the “Sales” column again. This is the range of cells that we want to apply our second condition to.
Step 6: Set the second criteria as “”. This means we only want to consider the cells that have a sales value less than 1700.
Step 7: Close the parentheses and press “Enter” to let Excel find the maximum sales value that meets both criteria.
The formula will look like this:
=MAXIFS(A2:A8,B2:B8,"Electronics",A2:A8,"
The result will show the maximum sales value of an Electronics product that has a sales value less than 1700. In this case, it is 1400.
Over to you now, try applying the Excel MAXIFS function to determine the maximum sales value for Clothing products following the same criteria. Challenge yourself to find the answer, which should be $1500. And remember, no peeking or cheating—test your Excel skills and uncover the solution on your own!
Best Free Office Suite: WPS Office
WPS Office has gained immense popularity due to its swift rise and its strong grasp of users' office suite requirements. It provides a comprehensive array of tools including word processing, presentation creation, spreadsheet handling, and even PDF editing capabilities.
What sets it apart is its remarkable integrated AI feature, a rarity among office suites. This AI integration brings about time-saving benefits, simplicity, and user-friendliness. The software is compatible with various file formats and devices, making it incredibly versatile. The best feature, however, remains to be that it is completely free of charge.
How-to:
If you are looking for the Excel MAXIFS function, you can try WPS Office. WPS Office is a free and all-in-one office suite that offers a variety of tools to work with data, such as the MAXIFS function. It is compatible with Microsoft Office, Google Docs, and Adobe PDF, so you can easily switch between different file formats. It is also available on multiple platforms, including Windows, Linux, and Mac.
To use the MAXIFS function in WPS Office, you can follow these steps:
Let’s look at the sales record of an online store. The sales record shows three categories: Clothing, Footwear, and Eyewear. In this sales record, we will try to calculate the max sales in each category using the MAXIFS function.
Step 1: Go to an empty cell and enter the “=MAXIFS(“.
Step 2: We will choose the “Sales” as our max_range.
Step 3: In our criteria_range, we will choose the “Category” column.
Step 4: Set the criteria as “Clothing”. This will help us find the max sales in the “Clothing” category.
Step 5: Press “Enter” for results.
Step 6: Repeat the steps for “Footwear” and “Eyewear”.
WPS Office offers a simple and user-friendly office suite interface for users to conduct easy data analysis. The MAXIFS function is just one of the many features that WPS Office provides for free. You can download WPS Office now and explore its other functions and templates.
FAQs:
Q1. What is the alternative to MAXIFS in Excel?
One alternative to the MAXIFS function in Excel is the AGGREGATE function. The AGGREGATE function can perform various calculations, such as MAX, MIN, AVERAGE, SUM, etc., while ignoring errors or hidden rows. To use the AGGREGATE function as an alternative to MAXIFS, you can use the following syntax:
=AGGREGATE (14,6,max_range/ (criteria_range=criteria),1)
This formula will return the maximum value in the max_range that meets the criteria specified in the criteria_range. The 14 argument indicates that the MAX function is used, the 6 argument indicates that errors are ignored, and the 1 argument indicates that the largest value is returned. You can also use multiple criteria by multiplying them together, such as:
=AGGREGATE (14,6,max_range/ ( (criteria_range1=criteria1)* (criteria_range2=criteria2)),1)
This formula will return the maximum value in the max_range that meets both criteria1 and criteria2.
Q2. Why does Excel MAXIF return 0?
There are some situations where MAXIFS may return 0 instead of the expected value. Some of the possible reasons are:
None of the cells in the range argument satisfy the criteria.
The range argument and all of the criteria_range arguments are not the same size.
Q3. Does MAXIFS work with text?
The MAXIFS function in Excel is a useful tool to find the maximum value among cells that meet one or more criteria. You can use text criteria with the MAXIFS function to filter the cells based on their text values. For example, you can use MAXIFS to find the maximum sales amount for a specific product, region, or customer.
Practice The Excel MAXIFS Function on WPS Office
The Excel MAXIFS function stands as a valuable tool when the objective is to obtain the maximum value under specific conditions. While the function itself is user-friendly, practicing it within the WPS Spreadsheet can greatly enhance your mastery of the function. Taking the time to practice will undoubtedly enhance your understanding of this concept. Download WPS Office and enjoy the full benefits of MAXIFS and other advanced functions.
