How to use the Excel MAXIFS Function
How to use the Excel MAXIFS Function
Learn how to use the MAXIFS function in Microsoft Excel. This tutorial demonstrates how to use Excel MAXIFS with our easy to follow examples and takes you step-by-step through the different options when entering your formula.
MAXIFS Function (and sample data):
https://www.techonthenet.com/excel/fo…
Excel Functions/Formulas:
https://www.techonthenet.com/excel/fo…
Excel Tutorials:
https://www.techonthenet.com/excel/in…
Don’t forget to subscribe to our channel for more great Excel tutorials!
Visit us at: https://www.techonthenet.com
or follow us on:
Facebook: https://www.facebook.com/techonthenetcom
Twitter: https://twitter.com/tech_on_the_net
Pinterest: https://www.pinterest.com/techonthene…
Content
0.03 -> the max ifs function is a new
2.639 -> statistical function that has been
4.74 -> introduced in Excel 2019 and office 365
9.559 -> the max ifs function allows you to find
12.57 -> the largest value in a range after
14.429 -> applying one or more criteria so let's
18.24 -> get started
19.14 -> here we have a table of data that
21.539 -> consists of year product and cost
24.029 -> columns for our first example let's find
27 -> the maximum costs for the product
29.01 -> oranges we'll begin by entering the max
31.8 -> ifs command as you can see that max ifs
34.77 -> function takes a series of parameters
36.899 -> the first parameter is the range of
39.45 -> cells that contains the largest or
41.64 -> maximum value in this example we'll use
44.85 -> range c2 to c10 which is our cost column
49.02 -> since we want to find the maximum cost
51.71 -> the second parameter is the range of
54.329 -> cells to apply your criteria against in
56.789 -> this example we'll enter the range b2 to
60.3 -> be 10 this is the range of cells that
63.239 -> will test to see if they meet our
64.769 -> criteria the third parameter is the
67.53 -> criteria that will be applied against
69.6 -> the range b2 to be 10 in our example
73.14 -> will enter oranges in quotes the max ifs
76.74 -> function will test each value in the
79.02 -> range b2 to be 10 to see if it is the
82.049 -> product called oranges since we only
85.619 -> want to apply one criteria these are all
88.32 -> of the parameters we'll need so let's
90.329 -> see what the max of function returns you
93.39 -> should see the value $15.99 appear this
97.59 -> is the largest cost value for the
99.54 -> product oranges
102.229 -> now let's see how to apply two criteria
105.17 -> against the max ifs function so for this
108.289 -> example let's find the maximum cost for
110.869 -> oranges since the Year 2019 again we
114.799 -> will enter the max ifs command and start
117.02 -> with the same three parameters that we
118.97 -> entered in the first example by entering
121.52 -> the range c2 to c10 as the first
124.159 -> parameter b2 to be 10 as a second
127.46 -> parameter and oranges in quotes as the
129.95 -> third parameter now it's time to apply
132.59 -> our second criteria by entering two more
135.23 -> parameters since we only want to include
137.9 -> costs for oranges since 2019 we will
141.709 -> enter the range a 2 to a 10 as the
144.079 -> fourth parameter which contains our Year
146.72 -> values and greater than equals 2019 as
150.59 -> the final parameter the Maxis function
154.22 -> will test each value in the range a 2 to
156.799 -> a 10 to see if it is greater than or
159.349 -> equal to 2019 now let's see what this
163.04 -> formula returns you should see the value
166.989 -> $13.45 appear as the result in this
170.569 -> example only the maximum cost is
172.73 -> included if both the product is oranges
175.28 -> and the Year is greater than or equal to
177.799 -> 2019 next let's find the maximum cost
182.329 -> for the year 2020 so again we'll start
185.48 -> with the max if function and enter the
187.88 -> range c2 to c10 as the first parameter
191.049 -> but this time we'll enter the range a 2
194.06 -> to a 10 as the second parameter and 2020
198.29 -> in quotes as the third parameter the
201.38 -> Maxis function will test each value in
203.75 -> the range a 2 to a 10 and only include
206.78 -> values that are equal to the year 2020
209.389 -> this formula returns the value $7.95
213.56 -> since that was the largest cost value in
216.349 -> the year 2020 finally let's see how to
220.13 -> use wildcards in the max ifs function in
223.849 -> Excel there are two different wildcard
225.859 -> characters the asterisk and the question
228.38 -> mark
229.58 -> the Astra character allows you to match
231.56 -> any string of any length whereas the
234.92 -> question mark allows you to match only
236.75 -> on a single character let's use the
239.78 -> asterisk wildcard to find the maximum
242.15 -> cost of all products that start with the
244.25 -> letter A so again we will start with the
247.16 -> max ifs function and enter the range c2
250.07 -> to c10 as the first parameter then we'll
253.97 -> enter the range b2 to be 10 as the
256.73 -> second parameter which contains our
259.19 -> product values finally we'll enter AE
262.61 -> asterisk in quotes as the third
265.01 -> parameter since we are only looking at
267.38 -> products to start with the letter A now
270.5 -> let's see what results we get you should
273.41 -> see $12.50 appear as the result which is
277.16 -> the maximum cost for all products that
279.23 -> start with the letter A so this would
282.02 -> include products such as apples and
283.76 -> apricots and that covers our examples of
287.15 -> how to use excels max ifs function if
289.61 -> you would like to see more examples or
292.49 -> would like to download the sample day
294.14 -> that we used for this tutorial please
296.33 -> visit our website at tech on the Netcom
299.59 -> if you found our tutorial helpful please
302.35 -> leave a like on this video and don't
304.33 -> forget to subscribe to our YouTube
305.77 -> channel for more great Excel tutorials
Source: https://www.youtube.com/watch?v=pm8dPHuxhuw