MAXIFS and MINIFS in Excel: how to use it
MAXIFS gives the highest number from only the rows that meet your conditions, such as the highest revenue for Leeds. MINIFS gives the lowest.
Try the first exercises for freeIn the lesson AVERAGEIF and MAXIFS
What do MAXIFS and MINIFS do?
MAXIFS finds the highest number, but only among the rows that meet your conditions: the highest revenue in Leeds, the most expensive order from one customer, the best score in one class. MINIFS does the same for the lowest number.
It's built the same way as SUMIFS: first the column with the numbers, and then each time a column with what has to be in it. You can give one condition or more, and a row only counts if all the conditions are true.
When do you use MAXIFS and MINIFS?
- The highest revenue of one region: =MAXIFS(C2:C100,A2:A100,"Leeds").
- The lowest price of one product: =MINIFS(C2:C100,B2:B100,"Laptop").
- The best score in one class, only for those who were present: =MAXIFS(C2:C40,A2:A40,"10A",B2:B40,"present").
- The smallest order, leaving out the zeros: =MINIFS(C2:C100,C2:C100,">0").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The highest number from the rows that meet the conditions.
Filled in: =MAXIFS(C2:C6,A2:A6,"Leeds")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
MAXIFS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | MAXIFS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
max_range | The column with the numbers you want the highest of. It comes first. | C2:C6 |
criteria_range1 | The column for the first condition. | A2:A6 |
criteria1 | What has to be in that column, such as "Leeds" or ">500". | "Leeds" |
[criteria_range2, criteria2] | Optional: another pair of a column and what has to be in it. | left out |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
[ ] | Anything in square brackets can be left out. You never type the square brackets themselves. | |
... | You can give more, with a comma between each one. |
MAXIFS and MINIFS step by step
E1 contains =MAXIFS(C2:C6,A2:A6,"Leeds"). The higher of the two revenues for Leeds. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click E1 and type the function with a bracket.
=MAXIFS( - First select the numbers you want the highest of: the revenue in C2:C6. Type a comma.
=MAXIFS(C2:C6, - Select the column with the condition, the regions in A2:A6, type a comma and then what has to be in it: "Leeds".
=MAXIFS(C2:C6,A2:A6,"Leeds" - Close the bracket and press Enter: of the revenue figures 1200 and 410 from Leeds, 1200 is the highest.
=MAXIFS(C2:C6,A2:A6,"Leeds")
More examples
The lowest with MINIFS
E1 contains =MINIFS(C2:C6,A2:A6,"Leeds"). The lower of the two revenue figures from Leeds.
Two conditions
E1 contains =MAXIFS(C2:C6,A2:A6,"York",B2:B6,"Monitor"). The highest revenue from a monitor in York. A row only counts if both conditions are true.
No rows at all
E1 contains =MAXIFS(C2:C6,A2:A6,"Cambridge"). Cambridge isn't in the list. Then the function gives 0, not an error, so check that your condition is right.
From Excel 2019
Both functions exist from Excel 2019 and in Excel 365. In an older Excel they give #NAME?.
Where it often goes wrong
- The column with the numbers comes first, just like in SUMIFS.
- If no row meets the conditions, you get 0, not an error.
Practise this in the lesson AVERAGEIF and MAXIFS. The first exercises are free.
Frequently asked questions
What's the difference with MAX?
MAX looks at all the numbers. MAXIFS only looks at the rows that meet your conditions.