MAXIFS In Google Sheets

What Is MAXIFS Function In Google Sheets?

The MAXIFS in Google Sheets returns the maximum value that fulfils a single or multiple criteria in a selected dataset, except that the values must be numeric values.

The Google Sheets MAXIFS Function helps users project the highest values easily in a large dataset with multiple digit numeric values, such as population data, a company’s profit and loss data, etc.

For example, we have the population details of 10 cities which is a large data and not so easy to find the populated city. Therefore, we will use the MAXIFS function to find the maximum populated city within a selected state, here, California.

Select cell E2, enter the formula =MAXIFS(C2:C11,B2:B11,”California”) and press “Enter”, as shown below.

The output is shown above, i.e. the maximum populated city data from the California state is returned.

The MAXIFS in Google Sheets is a Statistical function that fetches the highest value from a selected dataset depending on a single or multiple criteria.
Since, there are 3 mandatory arguments in the formula, ensure to provide all the values or else the formula will not execute.
We can also use the combination of MAX+IF or MAX+IFS in place of the MAXIF or the MAXIFS functions, respectively.
We must press the keys Ctrl+Shift+Enter in Google Sheets while executing an array formula.
The function can be used to check single or multiple criterias using AND and OR logic.

Syntax

The syntax of the MAXIFS Formula in Google Sheets is,

The arguments of the MAXIFS Formula in Google Sheets are,

range: It is the cell range or the numeric values column where the maximum value will be determined. It is a mandatory argument.
criteria_range1: It is the cell range where the criterion1 will be evaluated. It is a mandatory argument.
criterion1: It is the number, expression or text that is to be evaluated as maximum. It is a mandatory argument.
[criteria_range2, …]: It is additional cell range to evaluate [criterion2, …]. It is an optional argument.
[criterion2, …]: It is the additional number, expression or text that is to be evaluated as maximum. It is an optional argument.

How To Use MAXIFS Function In Google Sheets?

We can use the MAXIFS in Google Sheets in two ways, as follows:

Access from the Google Sheets ribbon.
Enter the formula in the worksheet manually.

Method 1 – Access From the Google Sheets Ribbon →

Step 1: Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Statistical” option right arrow → select the “MAXIFS” function, as shown below.

Step 2: The “MAXIFS” formula appears, as shown below. Enter the argument as cell reference.

Method 2 – Enter the Formula in the Worksheet Manually →

Step 1: Select an empty cell for the output.

Step 2: Type =MAXIFS( in the cell, as shown below. [Alternatively, type =M or =MAX and double-click the MAXIFS from the Google Sheets suggestions.]

Step 3: Enter the arguments as cell values or cell references and close the brackets.

Step 4: Press Enter to view the outcome.

Examples

Let us consider some MAXIFS in Google Sheets examples for single and multiple criteria’s with AND and OR functions.

Example #1 – MAXIFS with One Criterion.

The dataset given below consists of employee details, such as their names, department, ID’s job role, etc. We will find the maximum salary in the finance departmentusing the MAXIFS with One Criterion.

The procedure to find the maximum salary in the finance departmentis,

Select cell G2, enter the formula =MAXIFS(E2:E11,B2:B11,”Finance”) and press “Enter”, as shown below.

We see the output above, out of two values, it returns the highest or the maximum. We have highlighted the cells that satisfy the criteria, for our reference.

Example #2 – MAXIFS with Multiple Criteria (AND Logic).

The dataset consists of laptop brands and the available units. Let us find the maximum value that satisfies multiple criteria’s using the MAXIFS with Multiple Criteria (AND Logic).

The procedure to find the maximum quantity available using the MAXIFS with Multiple Criteria (AND Logic) is,

Select cell A12, enter the formula =MAXIFS(C2:C9,A2:A9,”*Dell*”,B2:B9,”Dell”) and press “Enter”, as shown below.

Example #3 – MAXIFS with Multiple Criteria (OR Logic).

We will consider the Example #2 dataset, once again, that consists of laptop brands and the available units. Let us find the maximum value that satisfies multiple criterias using the MAXIFS with Multiple Criteria (OR Logic).

The procedure to find the maximum quantity available using the MAXIFS with Multiple Criteria (OR Logic) is,

Select cell C12, enter the formula =MAX(MAXIFS(C2:C9,B2:B9,”Dell”),MAXIFS(C2:C9,B2:B9,”HP”)) and press “Enter”, as shown below.

The OR logic can be found in multiple ways, in this example we have used two MAXIFS with single criteria, and then compared the return results using the MAX function. Hence, the result.

Example #4

Consider the products data and their units sold on different dates. We will find the maximum smartphones sold, using the MAXIFS in Google Sheets.

The procedure to find the maximum smartphone sold on all the dates is,

Select cell E2, enter the formula =MAXIFS(C2:C10,A2:A10,”Smartphone”) and press “Enter”, as shown below.

The output is “150”, i.e. the maximum smartphones sold on the dates. The highlighted values are the cells that satisfy the criteria, for our reference.

Important Things To Note

The MAXIFS Function when used with the AND function, compares all the values and returns the maximum numeric values, that meets all specified conditions.
The MAXIFS Function when used with the OR function, compares all the criterias separately, gets the final values and then finally returns the maximum numeric values, that meets all specified conditions.
In the formula as we must provide single or multiple criterias, we can either provide it as a cell reference for a cell or a criteria table or a direct value within double-quotes.
If we provide a criteria table, then, a few things to remember are as follows:
- If the criteria range is placed below the list ensure not to add information to the list, as it will push the cells down and return error as the cell references change. To avoid such situations, it’s better to add it at the right of the dataset, unless we do not add extra columns.
- The criteria range should not overlap the list. Ensure to leave a row or a column in-between the dataset and the criteria table, if we are creating the table below or beside the dataset, respectively.

Frequently Asked Questions (FAQs)

1) Why is the MAXIFS in Google Sheets not working?

A few reasons the MAXIFS Google Sheets Function may not work are,

One or all of the argument values are not provided or incorrectly entered.
The cell range we select is modified or deleted.
We have inserted the result cells below the dataset and new data is added in the dataset.
The cell value for the arguments we give directly are inserted with double-quotes.
There is combination of values, such as numeric values and dates are in the selected dataset.
When we combine MAXIFS function with other functions, if the result of the formula, that must serve as an argument is not numeric, then we will get an error.
The format of the result cells is different from the selected cell range data format.

2) What is an alternate way to insert the MAXIFS in Google Sheets?

We often forget in which category a function falls, here, the “MAXIFS” function. Then, we can insert the function as follows:

Choose an empty cell → select the “Insert” tab → click the “Function” option right arrow → click the “All” option right arrow → select the “MAXIFS” function, as shown below.

However, as always, entering the function manually is the best way to avoid confusion.
Also, we can use the same path to insert other MAXIFS functions, such as MAXA, MAX, etc. as they are found just above the MAX function option, as we can see in the above image.

3) Where else can we find the MAXIFS in Google Sheets?

Alternatively, we can find the Functions icon to insert the MAXIFS in Google Sheets by following the path shown below.

• Choose an empty cell → click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.

• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.

• Here, click the “Functions” option → click the “All” option right arrow → select the “MAXIFS” function, as shown below.

4) What is the way to insert the IF and the IFS function Google Sheets?

We can insert the Google Sheets IF function as follows:
Choose an empty cell for the output → select the “Insert” tab → click the “Function” option right arrow → click the “Logical” option right arrow → select the “IF” function, as shown below.

We can use the same path to insert the IFS function as well, as it is just below the IF function, as seen in the above image.

Download Template

This article must help understand the MAXIFS in Google Sheets formula and examples. You can download the template here to use it instantly.

MAXIFS in Google Sheets Download

MAXIFS In Google Sheets