How to use maxifs function in excel

July 26, 2022
1.0K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

A statistical function in Excel called MAXIFS delivers the highest value depending on one or more conditions from the supplied cells.

How to use MAXIFS function in excel online, 2016 and 2019:

It is a built-in Excel function that may be used in worksheets. Let's look at a few instances:

Assume that the three locations where our company works have provided us with their quarterly expenses. Let's look at the formula to be utilized in order to determine the highest cost for the North region:=MAXIFS( $D$5:$D$33, B$5:B$33, “North”)

Excel's MAXIFS function in the aforementioned example finds rows where the value in column B equals North and returns the highest value from the related values in column D.

The MAXIFS Function in this illustration finds rows where North is the value in column A and returns the highest value from the matching values in column C.How to use MAXIFS function in excel using second example:Take yet another illustration. We are looking for the student who received the highest possible scores while achieving Distinction in any course. Using the information below:

The formula to use will be =MAXIFS(B6:B16,C6:C16,”A”,E6:E16,”=Distinction”)

C10, C11, and C16 in criteria range1 fit the A criteria of the corresponding cells in criteria range2, while E10, E11, and E16 match the Distinction criteria. Finally, B11 displays the highest value among the matching cells in max range. The outcome is 50 as a result.

How to use MAXIFS function in excel using third example:Take this instance where we would utilise MAXIFS in conjunction with an array formula. For the following data, we want to utilise the MAXIFS formula, which will yield the most recent date for open tasks for each project in Column E:

Therefore, given the data sample above, we would anticipate seeing the dates 01/03/2017, 01/05/2017, and 01/01/2017 in Column F on all rows for Project A, Project B, and Project C, respectively.The formula that will be used is:

Because parentheses only function as an array formula, we must first input them by pressing Ctrl+Shift+Enter. Following that, we may slide it over the required range.

Note: This above written article is an attempt to show you how to use MAXIFS function in excel online, 2016 and 2019. This article can be applied in both windows and mac, if you have any of them.You just need to have a little understanding of how and which way things work and you are good to go with your work. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.