MAXIFS and MINIFS in Excel

Excel provides various functions and formulas to calculate the result. The formulas may vary depending on the user's requirements. For each working project, the calculations will differ. Excel functions are used to retrieve the result quickly, efficiently, and error-free. The MAXIFS and MINI FS are used to find the maximum and minimum values from the given data set. Here in this tutorial, the MAXIFS and MINI FS are explained briefly.

MAXIS Function

The MAXIFS function returns the maximum value with specified conditions or criteria.

Syntax

MAXIS (max_range, criteria_range1, criteria 1,[criteria_range2,criteria2],…)

Parameters

max_range - The range where the maximum value is determined

criteria_range1 - This parameter is used to evaluate the cells with the specified criteria.

criteria_range2- The additional criteria range is added if required. Around the range of 126 criteria is added in the function if required.

Examples:

Example 1: How to find the Maximum value from the given data set?

To find the Maximum value from the given data set using the MAXIFS function, the steps to be followed are,

1. Enter the required data in the worksheet.
2. Select a new cell and enter the formula as =MAXIFS(Criteria)
3. Press the Enter key. The result is displayed in the cell, which is the greater value in the given data set.
   

The product name is retrieved using the MAXIFS function and the maximum value. From the above worksheet, the MAXIFS returns the result as 90, which is the maximum amount of oranges sold by the seller. Seller A sold 50 oranges, C sold 70, E sold 10, and G sold 90, which is the maximum value.

MINI FS Function

The MINI FS function returns the minimum value with specified conditions or criteria

Syntax

MINI FS (min_range, range1, criteria 1,[range 2],[criteria 2],…)

Parameters

min_range - The range where the minimum value is determined

criteria_range1: This parameter is used to evaluate the cells with the specified criteria.

criteria_range2- The additional criteria range is added if required.

The MINI FS operator accepts dates, numbers, texts, logical operators, and wildcards

Examples

Example 1 : How to find the Minimum value from the given data set?

To find the Minimum value from the given data set using the MINIFS function, the steps to be followed are,

1. Enter the required data in the worksheet.
2. Select a new cell and enter the formula as =MINIFS (Criteria)
3. Press the Enter button. The result is displayed in the cell, the smallest value in the given data set.
   

The product name is retrieved using the MINIFS function and the minimum value. From the above worksheet, the MINI FS returns the result as 10, which is the minimum amount of oranges sold by the seller. Seller A sold 50 oranges, C sold 70, G sold, 90and E sold 10, which is the minimum value.

Example 2: How to find the Maximum value from the given data set with a specified value?

To find the Maximum value with specified data from the given data set using the MAXIFS function, the steps to be followed are,

1. Enter the required data in the worksheet.
2. Select a new cell and enter the formula as =MAXIFS (criteria)
3. Hit the Enter key. The result is displayed in the cell, which is the greater value among various specified data.
   

From the above worksheet, the MAXIFS function returns the value of 80, which is the maximum value in Grade 1. Grade 1 represents various data such as (50, 80, and 55).

Example 3 : How to find the Minimum value from the given data set with a specified value?

To find the Minimum value with specified data from the given data set using the MINIFS function, the steps to be followed are,

1. Enter the required data in the worksheet.
2. Select a new cell and enter the formula as =MINIFS (criteria)
3. Hit the Enter. The result is displayed in the cell, the minimum value among various specified data.
   

From the above worksheet, the MINIFS function returns the value of 50, which is the minimum value in Grade 1. Grade 1 represents various data such as (50, 80, and 55).

Example 4: How to find the Minimum value from the given data set with multiple criteria?

To find the Minimum value with multiple criteria from the given data set using the MINIFS function, the steps to be followed are,

1. Enter the required data in the worksheet.
2. Select a new cell and enter the formula as =MINIFS (criteria)
3. Press Enter. The result is displayed in the cell, the minimum value among various specified data.
   

From the above worksheet, the formula =MINIFS (C2:C10, B2:B10, "FEMALE") returns the value 78, the minimum value in the female data set.

Example 5: Here is an example of calculating the data with multiple criteria and the " GROUP " data in the above example.

The steps to be followed to use multiple criteria are as follows,

1. Enter the required data in the worksheet
2. Select a new cell and enter the formula as =MINIFS (criteria)
3. Press Enter. The result is displayed in the cell, the minimum value among various specified data.
   

From the above worksheet, the formula =MINIFS (C2:C10, B2:B10, "MALE", D2:D10, "B") displays the result as 40, which is the minimum value in the data MALE. Here multiple criteria are applied in the formula to find the result in the specified data.

The above worksheet will display the result as 40, which is the minimum value and lesser than 50.

Example 6: How to implement not equal to the operator in the MINIFS function?

The not equal to the operator is represented in the symbol "<>". The steps to be followed to use the not equal operator are as follows,

1. Enter the data in the required spreadsheet
2. Select a new cell and enter the formula as =MINIFS (criteria)
3. Press Enter. The minimum value is displayed in the cell based on the not equal to the operator.
   

From the above worksheet, the result is displayed as the minimum value of 40 where the data present in the cells C2:C10 and E2:E10 are not equal to the value white, and the minimum value is represented in the cell.

Example 8: Using concatenation in the formula, how to use a value from another cell?

To use concatenation in the formula, the steps to be followed are as follows,

1. Enter the data in the required spreadsheet
2. Select a new cell and enter the formula as =MINIFS (criteria)
3. Press Enter. The cell's minimum value is displayed based on the formula's concatenation.
   

The above worksheet shows the result as 67, the concatenation of the value present in D4, and compared with C2:C10 and E2:E10.

Example 9: How to use a wildcard in the formula?

The wildcard is represented in the symbol of (?), (*), or tilde. Here the question mark is matched with any of the characters, and the asterisk is matched with zero or any characters. Here in this example, an asterisk is used. The steps to be followed are,

1. Enter the data in the required spreadsheet
2. Select a new cell and enter the formula as =MINIFS (criteria)
3. Press Enter. The value is displayed as zero, where the symbol asterisk is used in the formula, which matches any character or zero.
   

From the above formula, the result will be displayed as 0, where the asterisk symbol in the formula matches with the zero. Similarly, the tilde character is an escape character used to find literal wildcards. The tilde is added before the wildcard symbols (?), (*), and tilde signs.

Summary

The tutorial above explains the various functions and methods for calculating the data using MINI FS and MAXIFS functions. The MINI FS and MAXIFS function is used to find the minimum and maximum values easily.