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.
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.
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")
Once you have added the formula to the cell, press Enter.
We are now done! It’s that simple.
Yes, it will still work even if only one criterion is specified.
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.
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.
