In this article:

Google Sheets MAXIFS Function: The Ultimate Guide for 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:

Maxifs google sheets

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")

Google sheets maxifs‍

3. Press Enter

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

Maxifs google sheets output

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:

Google sheets maxifs criteria in other cells

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")

Maxifs google sheets criteria applied to the same range‍

The formula works as is. 

Automate everything you track in spreadsheets with Lido
Learn more

Automate manual tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started