Subscribe
Home Craft How to Use MAXIFS and MINIFS Functions in Excel

How to Use MAXIFS and MINIFS Functions in Excel

by Staff
0 comment

Microsoft Office continues to enchant its users with its fancy, yet valuable formulas. There are probably many useful functions in Microsoft Excel that aren’t used on a daily basis, despite their utility.


The MAXIFS and MINIFS functions in Excel may be one of the least used functions, but their utility cannot be overlooked. If you want to understand the methodology, usage, and practicality of these two uber essential functions in Excel, follow along for some relevant examples explaining the utility of these two functions.


Prerequisites of MAXIFS and MINIFS Functions in Excel

The MAXIFS and MINIFS are two of the most useful functions in Excel, after their counterparts COUNTIF, COUNTIFS, SUMIF, and SUMIFS.

Even though the MAXIFS and MINIFS functions calculate the maximum and minimum values in a range of values, you can learn to use the COUNTIF, SUMIF, and their related functions to perform meaningful arithmetic tasks.

These functions are available in Office 365 and Office 2019 only; if you’re using an older version of Excel, you’ll need to update to one of these newer versions, before you can hope to use them.

Practice Dataset

You can use a sample dataset to understand the concepts of MAXIFS and MINIFS functions.

Order Date City Category Sub-Category Sales Profit
08-11-2016 Kentucky Furniture Bookcases 261.96 41.9136
08-11-2016 California Furniture Chairs 731.94 219.582
12-06-2016 Florida Office Supplies Labels 14.62 6.8714
11-10-2015 Florida Furniture Tables 957.5775 -383.031
11-10-2015 California Office Supplies Storage 22.368 2.5164
09-06-2014 California Furniture Furnishings 48.86 14.1694
09-06-2014 California Office Supplies Art 7.28 1.9656
09-06-2014 California Technology Phones 907.152 90.7152
09-06-2014 California Office Supplies Binders 18.504 5.7825
09-06-2014 California Office Supplies Appliances 114.9 34.47
09-06-2014 California Furniture Tables 1706.184 85.3092
09-06-2014 North Carolina Technology Phones 911.424 68.3568
15-04-2017 Washington Office Supplies Paper 15.552 5.4432

Syntax Structure

The MAXIFS and MINIFS functions in Excel find the maximum and minimum values in a range of cells, respectively. The syntax for these functions is as follows:

MAXIFS (max_range, criteria_range1, criteria1, ...)
MINIFS (min_range, criteria_range1, criteria1, ...)

Both functions take the following arguments respectively:

  • max_range/min_range: Since this is the first argument, you must define the range you want to evaluate. Imagine this value as the final result you seek from a list of options.
  • Criteria_range1: Define the range as reference within the formula.
  • Criteria1: Specify the criteria to look for within the criteria_range1 column.

You can optionally add more ranges and criteria arguments to the function as follows:

  • Criteria_range2: Define the second range of values to use as a reference within the formula.
  • Criteria2: Specify the dependent criteria to look for within the criteria_range2 column.

Since you are dealing with multiple layers of formulas and their individual arguments, you should always try to understand Excel formulas step by step, to ease yourself into the formula building process.

How to Use MAXIFS and MINIFS Functions With Numbers in Excel

The MAXIFS and MINIFS functions works with numbers, text, and date values. To illustrate the usage with numbers, you can consider the profit amounts (Column F) in the sample data set.

There are a variety of arguments you can use when defining the conditions. You can easily choose between a single condition and multiple conditions.

Working With a Single Condition

To find the maximum profit for a specific sub-category, say Tables, use the formula shown below:

=MAXIFS(F2:F14, D2:D14, "Tables")

The above formula takes the following arguments:

  • MAXIFS: Excel function to calculate the maximum value based on a condition.
  • F2:F14: The function returns the maximum value basis to the condition you specify.
  • D2:D14: The conditional lookup value is available within this column (Tables).
  • Tables: Value to look up within the lookup range.

On similar grounds, you can calculate the minimum profit for Tables by defining the range and criteria as follows:

=MINIFS(F2:F14, D2:D14, "Tables")

The maximum value is 85.3092 and the minimum value is -383.031.

Working With Multiple Conditions

There isn’t much variation in the code when working with multiple conditions. Instead of defining a single condition, you extend the formula to include another dependent condition. Use the formulas listed below to calculate the maximum and minimum Sales value for Furniture in California.

=MAXIFS(E2:E14, B2:B14, "California", C2:C14, "Furniture")
=MINIFS(E2:E14, B2:B14, "California", C2:C14, "Furniture")

Note the usage of two conditions within the formula. In layman terms, Excel filters the data for California and Furniture, before calculating the maximum and minimum sales values.

The maximum value is 1706.184 while the minimum value is 48.86.

Using Logical Operators Within Numbers

Logical operators are used in conjuction with MAXIFS and MINIFS functions to specify additional conditions that you can use to enhance the effectiveness of your calculations. The logical operators present in Excel are:

Operator Meaning
= Equal to
> Greater than
< Less than
>= Greater than equal to
<= Less than equal to

Now, if you want to use any of these operators to return a value based on a condition, you can use it as follows:

=MAXIFS(E2:E14, E2:E14,"<50")
=MINIFS(E2:E14, E2:E14,"<50")

This formula returns the maximum value of 48.86 from the Sales column. The second formula returns the minimum value, which is 7.28.

How to Use MAXIFS and MINIFS Functions With Dates in Excel

At times, when you need to search for values that fall within a date range, you can use the MAXIFS and MINIFS functions with ease. To find a value that falls after a specific date, you can use the following:

=MAXIFS(E2:E14, A2:A14, ">02-01-2016")
=MINIFS(E2:E14, A2:A14, ">02-01-2016")

As you might have guessed, the first formula returns the value 731.94, which is the maximum value between the order dates 02-01-2016 and 15-04-2017. Subsequently, the second formula returns the value 14.62, since the date conditions are the same as before.

If you want to expand this formula and include a range of dates, you can do so in the following manner:

=MAXIFS(E2:E14, A2:A14, ">02-01-2015", A2:A14, "<02-01-2017")
=MINIFS(E2:E14, A2:A14, ">02-01-2015", A2:A14, "<02-01-2017")

The former function returns the value 957.5775, while the latter returns the corresponding value of 14.62.

How to Use MAXIFS and MINIFS Functions With Text Values in Excel

There might not be much to look forward to when using the MAXIFS and MINIFS functions with text values. The result usually consists of a value for a specific condition specified within the formula range.

Suppose you want to calculate the maximum and minimum Sales values for California. To do so, you can specify the city name within the formula directly.

=MAXIFS(E2:E14, B2:B14, "California")
=MINIFS(E2:E14, B2:B14, "California")

When working with text criteria, make sure you match the case of the search value with the original text value. As expected, the maximum value for California is 1706.184, and the minimum value is 7.28.

Working With Conditional Functions in Excel

Excel offers a lot of conditional formulas, making it a cinch to work with different logical statements. Conditional statements like AVERAGEIF, SUMIF, SUMIFS, and other related functionalities make Microsoft Excel a delight for data analysts.

Even though the concept of these statements is more or less the same, a few differences make them stand out from the list of available functions.



Read the full article here

SaleBestseller No. 1
Apple AirPods Max Wireless Over-Ear Headphones. Active Noise Cancelling, Transparency Mode, Spatial Audio, Digital Crown for Volume Control. Bluetooth Headphones for iPhone - Green
Apple AirPods Max Wireless Over-Ear Headphones. Active Noise Cancelling, Transparency Mode, Spatial Audio, Digital Crown for Volume Control. Bluetooth Headphones for iPhone - Green
 Apple-designed dynamic driver provides high-fidelity audio; Active Noise Cancellation blocks outside noise, so you can immerse yourself in music
$449.99
Bestseller No. 3
Apple iPad Air 2, 64 GB, Space Gray (Renewed)
Apple iPad Air 2, 64 GB, Space Gray (Renewed)
Apple iOS 8; 9.7-Inch Retina Display; 2048x1536 Resolution; A8X Chip with 64-bit Architecture; M8 Motion Coprocessor
$185.00
SaleBestseller No. 4
2021 Apple 10.2-inch iPad (Wi-Fi, 64GB) - Silver
2021 Apple 10.2-inch iPad (Wi-Fi, 64GB) - Silver
Gorgeous 10.2-inch Retina display with True Tone; A13 Bionic chip with Neural Engine; 8MP Wide back camera, 12MP Ultra Wide front camera with Center Stage
$269.99
Bestseller No. 5
2022 Apple TV 4K Wi‑Fi with 64GB Storage (3rd Generation)
2022 Apple TV 4K Wi‑Fi with 64GB Storage (3rd Generation)
4K Dolby Vision and HDR10+ for vivid picture quality; Dolby Atmos for three-dimensional, theater-like sound
$123.49
Bestseller No. 7
Apple AirTag 4 Pack
Apple AirTag 4 Pack
Keep track of and find your items alongside friends and devices in the Find My app; Simple one-tap setup instantly connects AirTag with your iPhone or iPad
$94.98
Bestseller No. 8
Apple MacBook Air with Intel Core i5, 1.6GHz, (13-inch, 4GB,128GB SSD) - Silver (Renewed)
Apple MacBook Air with Intel Core i5, 1.6GHz, (13-inch, 4GB,128GB SSD) - Silver (Renewed)
1.6 GHz dual-core Intel Core i5 (Turbo Boost up to 2.7 GHz) with 3 MB shared L3 cache; 13.3-Inch (diagonal) LED-backlit Glossy Widescreen Display, 1440 x 900 resolution
$299.99
Bestseller No. 9
Apple Of My Eye
Apple Of My Eye
Amazon Prime Video (Video on Demand); Amy Smart, Burt Reynolds, Liam McIntyre (Actors); Castille Landon (Director) - Castille Landon (Writer) - Dori A. Rath (Producer)
$3.99
SaleBestseller No. 10
Apple 35W Dual USB-C Port Compact Power Adapter ​​​​​​​
Apple 35W Dual USB-C Port Compact Power Adapter ​​​​​​​
The compact size and folding prongs make it easy to pack and store.; Charging cable sold separately.
$52.00

You may also like

Leave a Comment

Iman Hearts is one of the biggest lifestyle news and articles portals, we provide the latest news and articles about family, lifestyle, entertainment, and many more, follow us to get the latest news about what matters to you.

 

© 2022 Iman Hearts. All rights reserved. Sitemap