# Google Sheets MAXIFS Function: The Ultimate Guide for 2024

May 8, 2024

## When to Use Google Sheets MAXIFS Function?

The MAXIFS function is best used when you want to find the maximum value in a column of values, while filtering the range through a series of criteria applied to other columns in the same range. For example, you can use MAXIFS to find the maximum value you want to find in Column A while using the other information in Column B and C to narrow down the list.

## How to Use Google Sheets MAXIFS Function

### 1. Identify the criteria to apply to the range

For example, we have the following range:

We want to find out the maximum number of items sold per product. We have three columns: the item, the store, and the price per item. We can use two or three of these as criteria. We will use the columns for the store and the price per item as criteria in filtering the rows. To be specific, we want to apply the following criteria:

Store: A

Price per item: More than \$4.00

The range of the entire sheet is A4:D36, and we will place the formula to cell B1.

### 2. Use the formula =MAXIFS(range, criterion1_range, criterion1, criterion2_range, criterion2, …)

The MAXIFS function has the following syntax:

=MAXIFS(range, criterion1_range, criterion1, criterion2_range, criterion2, …)

Where the function will look for the maximum value in the given range,  as long as it fulfills criterion1 applied in criterion1_range, criterion2 applied in criterion2_range, and so on.

For the example above, we want to apply the following criteria:

Store: A

Price per item: More than \$4.00

Since the Store criterion is simply an exact match, we simply enclose the string in double quotes:

“A”

For the Price per item criterion, we enclose the condition in double quotes:

“>4”

Given that Store is in Column B and Price per item is in Column C, we set up the formula as follows:

=MAXIFS(D4:D36,B4:B36,"A",C4:C36,">4")

## 3. Press Enter

Once you have added the formula to the cell, press Enter

We are now done! It’s that simple.

## FAQs

### Can MAXIFS Work with One Criterion?

Yes, it will still work even if only one criterion is specified.

### Can a Criterion be Stored in Other Cells Instead of Being Added in Formula?

Yes. Simply set the cell address containing the criterion for each case. If you want some flexibility, you need some workarounds. For example, if you want to give the user flexibility to specify whether to make it greater or less than a number, you need to combine the symbol with the number using a CONCAT function:

What basically happened is that we inserted dropdown boxes at cells B2 and D2. Cell B2 contains the Store criterion while cell D2 contains the symbol for comparison (greater than, less than, etc.,) and cell E2 contains the number to compare. So if we have the following criteria:

Store: C

Price per item: Greater than \$3.00

The formula stored in D1 is

=MAXIFS(D5:D37,B5:B37,B2,C5:C37,CONCAT(D2,E2))

Note that the Criterion2  uses the CONCAT function to concatenate the strings stored in D2 and E2.

### Can a Criterion be Applied in the Same Range where the Maximum is Searched?

Yes. Using the same dataset as above, let us have the following set of criteria:

Store: A

Number of items: Less than 15

The formula becomes

=MAXIFS(D5:D37,B5:B37,"A",D5:D37,"<15")

The formula works as is.

Get Google Sheets productivity and automation tips delivered straight to your inbox