*The article shows a few different ways to get the max value in Excel based on one or several conditions that you specify.*

In our previous tutorial, we looked at the common uses of the MAX function which is designed to return the largest number in a dataset. In some situations, however, you may need to drill down into your data further to find the max value based on certain criteria. This can be done by using a few different formulas, and this article explains all possible ways.

## Excel MAX IF formula

Until recently, Microsoft Excel did not have a built-in MAX IF function to get the maximum value based on conditions. With the introduction of MAXIFS in Excel 2019, we can do conditional max an easy way.

In Excel 2016 and earlier versions, you still have to create your own **array formula** by combining the MAX function with an IF statement:

*criteria_range*=

*criteria*,

*max_range*))}

To see how this generic MAX IF formula works on real data, please consider the following example. Supposing, you have a table with the long jump results of several students. The table includes the data for three rounds, and you are looking for the best result of a particular athlete, say Jacob. With the student names in A2:A10 and distances in C2:C10, the formula takes this shape:

`=MAX(IF(A2:A10="Jacob", C2:C10))`

Please keep in mind that an array formula must always be entered by pressing the Ctrl + Shift + Enter keys simultaneously. As the result, it is automatically surrounded with curly brackets like shown in the screenshot below (typing the braces manually won't work!).

I real-life worksheets, it's more convenient to input the criterion in some cell, so that you can easily change the condition without changing the formula. So, we type the desired name in F1 and get the following result:

`=MAX(IF(A2:A10=F1, C2:C10))`

### How this formula works

In the logical test of the IF function, we compare the list of names (A2:A10) with the target name (F1). The result of this operation is an array of TRUE and FALSE, where the TRUE values represent names that match the target name (Jacob):

`{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}`

For the *value_ if_true* argument, we supply the long jump results (C2:C10), so if the logical test evaluates to TRUE, the corresponding number from column C is returned. The *value_ if_false* argument is omitted, meaning will just have a FALSE value where the condition is not met:

`{FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE}`

This array is fed to the MAX function, which returns the maximum number ignoring the FALSE values.

Tip. To see the internal arrays discussed above, select the corresponding part of the formula in your worksheet and press the F9 key. To exit the formula evaluation mode, press the Esc key.

## MAX IF formula with multiple criteria

In situation when you need to find the max value based on more than one condition, you can either:

Use nested IF statements to include additional criteria:

*criteria_range1*=

*criteria1*, IF(

*criteria_range2*=

*criteria2*,

*max_range*)))}

Or handle multiple criteria by using the multiplication operation:

*criteria_range1*=

*criteria1*) * (

*criteria_range2*=

*criteria2*),

*max_range*))}

Let's say you have the results of boys and girls in a single table and you wish to find the longest jump among girls in round 3. To have it done, we enter the first criterion (female) in G1, the second criterion (3) in G2, and use the following formulas to work out the max value:

`=MAX(IF(B2:B16=G1, IF(C2:C16=G2, D2:D16)))`

`=MAX(IF((B2:B16=G1)*(C2:C16=G2), D2:D16))`

Since both are array formulas, please remember to press Ctrl + Shift + Enter to complete them correctly.

As shown in the screenshot below, the formulas produce the same result, so which one to use is a matter of your personal preference. For me, the formula with the Boolean logic is easier to read and build – it allows adding as many conditions as you want without nesting additional IF functions.

### How these formulas work

The first formula uses two nested IF functions to evaluate two criteria. In the logical test of the first IF statement, we compare the values in the Gender column (B2:B16) with the criterion in G1 ("Female"). The result is an array of TRUE and FALSE values where TRUE represents data that match the criterion:

`{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE}`

In a similar fashion, the second IF function checks the values in the Round column (C2:C16) against the criterion in G2.

For the *value_if_true* argument in the second IF statement, we supply the long jump results (D2:D16), and this way we get the items that have TRUE in the first two arrays in corresponding positions (i.e. the items where the gender is "female" and round is 3):

`{FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52}`

This final array goes to the MAX function and it returns the largest number.

The second formula evaluates the same conditions within a single logical test and the multiplication operation works like the AND operator:

When the TRUE and FALSE values are used in any arithmetic operation, they are converted into 1's and 0's, respectively. And because multiplying by 0 always gives zero, the resulting array has 1 only when all the conditions are TRUE. This array is evaluated in the logical test of the IF function, which returns the distances corresponding to the 1 (TRUE) elements.

## MAX IF without array

Many Excel users, including me, are prejudiced against array formulas and try to get rid of them wherever possible. Luckily, Microsoft Excel has a few functions that handle array natively, and we can use one of such functions, namely SUMPRODUCT, as kind of "wrapper" around MAX.

The generic MAX IF formula without array is as follows:

*criteria_range1*=

*criteria1*) * (

*criteria_range2*=

*criteria2*) *

*max_range*))

Naturally, you can add more range/criteria pairs if needed.

To see the formula in action, we will be using the data from the previous example. The aim is to get the maximum jump of a female athlete in round 3:

`=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))`

This formula is competed with a normal Enter keystroke and returns the same result as the array MAX IF formula:

Casting a closer look at the above screenshot, you can notice that invalid jumps marked with "x" in the previous examples now have 0 values in rows 3, 11 and 15, and the next section explains why.

### How this formula works

As with the MAX IF formula, we evaluate two criteria by comparing each value in the Gender (B2:B16) and Round (C2:C16) columns with the criteria in cells G1 and G2. The result are two arrays of TRUE and FALSE values. Multiplying the arrays' elements in the same positions converts TRUE and FALSE into 1 and 0, respectively, where 1 represents the items that meet both criteria. The third multiplied array contains the long jump results (D2:D16). And because multiplying by 0 gives zero, only the items that have 1 (TRUE) in the corresponding positions survive:

`{0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}`

In case *max_range* contains any text value, the multiplication operation returns the #VALUE error because of which the entire formula won't work.

The MAX function takes it from here and returns the largest number that meets the specified conditions. The resulting array consisting of a single element {4.63} goes to the SUMPRODUCT function and it outputs the max number in a cell.

Note. Because of its specific logic, the formula works with the following caveats:

- The range where you search for the highest value must contain only numbers. If there are any text values, a #VALUE! error is returned.
- The formula cannot evaluate the "not equal to zero" condition in a negative data set. To find max value ignoring zeros, use either a MAX IF formula or MAXIFS function.

## Excel MAX IF formula with OR logic

To find the max value when **any** of the specified conditions is met, use the already familiar array MAX IF formula with the Boolean logic, but add the conditions instead of multiplying them.

*criteria_range1*=

*criteria1*) + (

*criteria_range2*=

*criteria2*),

*max_range*))}

Alternatively, you can use the following non-array formula:

*criteria_range1*=

*criteria1*) + (

*criteria_range2*=

*criteria2*)) *

*max_range*))

As an example, let's work out the best result in rounds 2 and 3. Please pay attention that in the Excel language, the task is formulated differently: return the max value if round is either 2 or 3.

With the rounds listed in B2:B10, the results in C2:C10 and criteria in F1 and H1, the formula goes as follows:

`=MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))`

Enter the formula by pressing the Ctrl + Shift + Enter key combination and you will get this result:

The max value with the same conditions can also be found by using this non-array formula:

`=SUMPRODUCT(MAX(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))`

However, we need to replace all "x" values in column C with zeros in this case because SUMPRODUCT MAX only works with numeric data:

### How these formulas work

The array formula works exactly the same way as MAX IF with AND logic except that you join the criteria by using the addition operation instead of multiplication. In array formulas, addition works as the OR operator:

Adding up two arrays of TRUE and FALSE (which result from checking the values in B2:B10 against the criteria in F1 and H1) produces an array of 1's and 0's where 1 represents the items for which either condition is TRUE and 0 represents the items for which both conditions are FALSE. As the result, the IF function "keeps" all the items in C2:C10 (*value_if_true*) for which any condition is TRUE (1); the remaining items are replaced with FALSE because the *value_if_false* argument is not specified.

The non-array formula works in a similar manner. The difference is that instead of IF's logical test, you multiply the elements of the 1's and 0's array by the elements of the long jump results array (C2:C10) in the corresponding positions. This nullifies the items that do not meet any condition (have 0 in the first array) and keeps the items that meet one of the conditions (have 1 in the first array).

## MAXIFS – easy way to find highest value with conditions

The users of Excel 2019, 2021 and Excel 365 are free from the trouble of taming arrays to build their own MAX IF formula. These versions of Excel provide the long-awaited MAXIFS function that makes finding the largest value with conditions child's play.

In the first argument of MAXIFS, you enter the range in which the maximum value should be found (D2:D16 in our case), and in the subsequent arguments you can enter up to 126 range/criteria pairs. For example:

`=MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)`

As shown in the screenshot below, this simple formula has no problem with processing the range that contains both numeric and text values:

For the detailed information about this function, please see Excel MAXIFS function with formula examples.

That's how you can find max value with conditions in Excel. I thank you for reading and hope to see you on our blog next week!

## Practice workbook for download

Excel MAX IF formula examples (.xlsx file)

## 120 comments

Need a formula that gives the date on which the maximum units were sold as per the highest, second highest and so on. and the corresponding units sold

Hello! Try to use the recommendations described in this guide: Get data associated with the n-th largest value. This should solve your task.

I'm trying to combine IF, FILTER, and MAXIF to essentially get the sum of all numbers (Column B) for each particular group (Column A) below the indicated threshold, taking the largest numbers first. At the same time however, I am wanting to return a simple "Yes" or "No" in Column D as to which numbers (Column B) should be included and summed together for each particular group to be below the threshold.

Although I considered embedding my IF formula with a subtraction of simple LARGE formulas for each data set, there is variable amount of group names and associated numbers, making it difficult and time consuming to determine how many different times I would have to repeat the LARGE function. In turn I figured it would make sense to potentially embed the MAXIF formula

If you have any insight on how I could use MaxIF(s) to accomplish my goal, please let me know.

I have provided a sample data set below as an example witht he following specific question

How can I determine which items (with a yes or no) from Column B (number), specific to their criteria in Column A (group name), need to be added together to get below the indicated threshold in column C. taking the largest numbers first for each group.

Group Name - Column A

Group 1

Group 1

Group 1

Group 1

Group 2

Group 2

Group 2

Group 2

Group 2

Number - Column B

100

347

532

431

2500

37890

4766

5467

2345

Threshold - Column C

450

450

450

450

3000

3000

3000

3000

Hi! If I understand your task correctly, the following formula should work for you:

=SUMPRODUCT((A1:A10="group 1") * (B1:B10>C1) * (B1:B10))

You can find the examples and detailed instructions here: Excel SUMPRODUCT function with multiple criteria.

Hi - I appreciate your response.

I don't believe that the formula above is what I am looking for.. In using the formula above - I am returned the sum product of 532 for Group 1 - which leaves a remaining amount associated with Group 1 above the threshold of 450 (100 +347 +431) > 450.

Furthermore - I am trying to determine with a yes or no which numbers corresponding to group 1 would need to be included together to sum below the threshold rather than the numerical value (sum of column B) that achieves the the threshold amount - am I able to do this by embedding by embedding the sum prodcut formula with an if?

In all - I am looking to return in column D with my formula the following results for Group 1 as an example

Column D

No

No

Yes

Yes

In this result the amounts of 532 and 431 are the only numbers needed to add together to reduce the population amount of Group 1 below the threshold of 450 for Group 1 as the remaining numbers associated are only 447 (347+100)

Let me know if this information is helpful and if you have any other questions surrounding my problem. Thanks!

I'm looking to possibly combine FILTER and UNIQUE to return every row that has a maximum in Column B for each unique value of Column A and every row that has a maximum in Column C for each unique value of Column A.

I was trying some version of the following formula, aiming for the max of Column B first, then adding in Column C once I got it working. However I still haven't got it working so I haven't tried adding in Column C yet.

=FILTER(A1:C13,B1:B13=MAXIFS(B1:B13,A1:A13,UNIQUE(A1:A13)))

Given:

A 3.54 0.39

A -6.04 1.02

A 6.16 0.79

A 6.16 0.86

B 18.33 0.92

B 4.99 0.85

B 12.26 0.4

B 5.28 0.33

B 15.22 -6.7

C 5.55 0.59

C 1.36 0.94

C -10.81 -4.84

C -4.63 -6.99

Desired Result:

A 6.16 0.79

A 6.16 0.86

A -6.04 1.02

B 18.33 0.92

C 5.55 0.59

C 1.36 0.94

(I typed all these values manually, so I hope I didn't mistype my desired result.)

I was also thinking maybe some form of INDEX+MATCH using multiple criteria and a column = 0 in the INDEX function to return entire rows.

If you could provide any insight, I would be very grateful. Thank you!

Hello! You can get the maximum values you want for "A" with this formula:

=FILTER(A1:C13, (B1:B13=MAX(FILTER(B1:B13,A1:A13="A"))) + (C1:C13=MAX(FILTER(C1:C13,A1:A13="A"))))

That's pretty close, but I was really hoping not to have to hard-code "A", "B", etc... This is going to be used to filter software results output and the labels in Column A are just placeholders. In reality, I have no way of knowing ahead of time what those labels will be. My goal is to find all the unique values in Column A using UNIQUE (or something similar) and then find the max of columns B and C based on that list of unique labels.

Maybe HSTACK or VSTACK could help? I'm not sure, I'm really new to these formulas and still learning.

I might just have to make something using VBA. I was excited when I found out about the dynamic array functions because I thought I could accomplish what I need with just formulas.

Hi! You can create a list of unique values using the UNIQUE function. Then make references in this formula to each of these values.

Your problem can be solved with the recursive function LAMBDA. Here is a detailed instruction: How to write recursive LAMBDA function in Excel with examples.

Creating such functions, as well as VBA code, is beyond the help we provide on this blog. But I will try to think about your problem if I have some free time.

Oh, yeah, no worries about any VBA. That's on me.

I've never used LAMBDA functions before, but I do know that they exist and have seen them in passing. I will investigate further. Thank you for the suggestion.

114 315 116 211 118 5 81 199 122

this is the data on A22:I21 which is the sum of daily expenses. i wanted to highlight all the time the highest number below 194. (Ignore all the number above 194 all the time), i have used the below formula, but still not highlighting the

AND(A22:I22<=194, $A22:$I22=MAX($A22:$I22)), this formula supposed to highlight 122 as this is the highest number under 194

Please re-check the article above since it covers your task. Use MAXIFS function as described above:

=MAXIFS(A22:I22,A22:I22,"<194")

Or use MAX IF:

=MAX(IF(A22:I22<194,A22:I22))

Hi there, please help, I am encountering an issue to get my desired output.

I have a very large set of data, and and a lot of them are duplicates. For each set of duplicates, how do I only return the highest value of rating? Also, if the set of duplicates contains the same high rating, it will only return 1 value for each duplicates although there are multiple high rating with same value.

Example:

*Data* *Rating*

DATA1 2

DATA1 1

DATA1 3

DATA2 1

DATA2 2

DATA3 3

DATA3 2

DATA3 3

Desired output would be:

*Data* *Rating*

DATA1

DATA1

DATA1 3

DATA2

DATA2 2

DATA3 3

DATA3

DATA3

I have trying for almost 2 weeks but I just can't seem to figure it out. Please help!!! Thank you

Hi! Your desired result does not match the task you described. It contains duplicates. Describe exactly what you want to do.

In column a having multiple common number and column b having lowest to largest number for each common number than how to identify each common number last 4 highest number

Your task is not completely clear to me. To understand what you want to do, give an example of the source data and the desired result.

How will i get preferred supplier name from below table for maximum value?

Item Supplier1(Name) Supplier1(Value) Supplier2(Name) Supplier2(Value) Supplier3(Name) Supplier3(Value) Preferred Supplier name (max value)

12345 Andrew 1000 Thomas 5000 Phillip 3000 ??????

Hi! Use the WRAPROWS function to convert a string to an array with two columns. Use the CHOOSECOLS function to get the desired column from this array, and use INDEX MATCH to search for it.

Try this furmula:

=INDEX(CHOOSECOLS(WRAPROWS(B1:G1,2),1), MATCH(TRUE, CHOOSECOLS(WRAPROWS(B1:G1,2),2)=MAX(CHOOSECOLS(WRAPROWS(B1:G1,2),2)), 0))

hello.

i am trying to writing formula like.

i have a name Albert in cell "g1", and i have a data in column A column B and Column c

i am writing the formula in h1 column

Column A = Name of customer

Column B = Phone number of customer

Column C.= Date of sale

now in column A each customer coming many time

column b mobile number of customer( note here: number is different but name can be same)

now i want to find the the last date of sell from column C.

and also want to find the first date of sale

hope you understand what i mean. i am actually not very good in english.

thank you

Hello! To find the last date, use these guidelines and examples: Excel INDEX MATCH with multiple criteria.

For example:

=INDEX(С2:С13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (MAX(C2:C13)=C2:C13), 0))

i am close but not actually what i want. can i show you my data if this possible. it might me help to get exact what i actually want.

hı am tryıng to get an average for 3 best quızzes usıng ıf functıon (there are fıve quızzes rıght e.g

name q1 q2 q3 q4 q5

hassan 65 87 34 90 68

Hi! To get the 3 highest values, use the LARGE function. Then use the AVERAGE function to find the average value.

For example,

=AVERAGE(LARGE(B2:F2,1),LARGE(B2:F2,2),LARGE(B2:F2,3))

I am trying, on a different file (not test file), to return the most recent name entry (all text) associated with a certain ID (text and number ID) but both Maxif and Large functions are returning null values. do you have any tips?

This is between sheets

And i have MAXIFS that are returning other fields correctly with numbers it is only failing in all text entries

Hi!

Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

Final sheet

Person ID - Name - Most recent Location (Multiple possible entries) - Most recent trip (Date)

1 - VLookup (Working) - ???? - MAXIF (working)

Raw data Sheet has all 4 of these areas + lots more but over 280,000 data points. i can pull in the most recent date associated with the person via MAXIF and their name from Vlookup. But the most recent trip destination being all text returns a null value. The person may have been to 30+ locations so a lookup only returns the first data point not the most recent data point (not always the same - updated weekly).

Let me know if this makes sense

Hello!

You can find the last match using the XLOOKUP function. See this article for instructions and examples: XLOOKUP function in Excel - powerful successor of VLOOKUP. I hope my advice will help you solve your task.

Thank you so much for this example. I am having some trouble returning a max date. I have tried a formula to return the most resent date for Jon J as =MAX(IF(tbl_data[Name]=E1,tbl_data[date]. When I try this formula, I do not get the most resent date for Jon, I get an error. How do I return the most recent entry for "Jon J"? Ultimately I am trying to use a formula to return the most recent date and score for a selected person. Any help anyone can provide would be very appreciated. Thank you all.

Example below

A B C D E

1 Date Name Score Name Jon J

2 1/1/2023 Jon J 70 Date

3 1/1/2023 Bob B 50 Score

4 1/5/2013 Jon J 72

5 1/5/2013 Bob B 55

6 1/7/2023 Jon J 66

7 1/7/2023 Bob B 60

8 1/9/2023 Jon J 54

Sorry my example combined and makes it hard to read. I am trying to space the example out again. A-E are the columns and 1-8 are the rows.

Date Name Score Name Jon J

1/1/2023 Jon J 70 Date most recent date

1/1/2023 Bob B 50 Score most recent score

1/5/2023 Jon J 72

1/5/2023 Bob B 55

1/7/2023 Jon J 66

1/7/2023 Bob B 60

1/9/2023 Jon J 54

Hello!

I can't use your references, but the formula =MAX(IF(B2:B8=E1,A2:A8)) returns the correct result 1/9/2023

Alexander,

Thank you so much for replying. When I use the formula, I keep getting the date 1/0/1900

Alexander,

I was able to get it to work. In this example, how would you get the second max date of 1/7/2023?

Thank you again for all of your help.

Hi!

Instead of the MAX function, use the LARGE function.

Here is the article that may be helpful to you: LARGE IF formula in Excel: get n-th highest value with criteria

=LARGE(IF(B2:B8=E1,A2:A8),2)

Alexander,

Thank you so much! This has been very helpful!

A B C

3.57 5 joe

3.26 1 jay

3.35 4 dany

3.43 3 ria

3.00 3 mary

In the above example, I want to take top 3 values from column B and it has to fetch the corresponding name from column C.

If the value is same in column B, based on the corresponding highest value in the A column, it should fetch the name in C.

Can someone help how to do that?

Hi!

Unfortunately, I do not understand what result you want to get.

I have race teams with 6 members. Each team must have at least 1 person of each sex. To get the team total, I want the fastest Male + Fastest Female + 2 fastest remaining. I can't just drop the 2 slowest racers; they may be the only Male or Female. I'm able to get the Fastest M & F using the SMALL function, but can't come up with the logic for the next 2. Any thoughts?

Is there a way to add a condition or threshold to the Max value that is returned?

Using the example above how can I return the longest jump for all Males less then 5.56.

Hi!

Use the MAXIFS function as described in the last paragraph of the article above. For example,

=MAXIFS(D2:D16, B2:B16, G1, D2:D16, "<"&G2)

Hi all,

quick question

I have a table with similar data but different time

Name Time

A 10/31/2022 12:54:00

B 10/27/2022 9:22:00

A 10/26/2022 13:56:00

B 10/21/2022 9:39:00

C 10/20/2022 10:34:00

D 10/19/2022 4:06:00

How do i get to the earliest time for each name?

I tried =MAX(IF(A$1:A$6=A1,B$1:B$6))

But it give me the newest date of the all sheet and not newest date for Name A

Thanks

Hi!

To get the minimum value by conditions, use the MINIFS function.

Try this formula:

=MINIFS(B1:B6,A1:A6,"A")

hi, what is incorrect here (try to get latest date): =MAX(IF((Totals!B12:B1390={"*Aa*","Bb"})*(Totals!H2:H1390="*Cc*")* (Totals!I2:I1390""),Totals!I2:I1390))

Hi!

Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

hi i would like to ask for correct formula for my following hypothetical problem:

so i want to make a chart to measure...say, sport player performance. we have Player A that can score 5 points but usually either being tired or whatever will be benched after round 3 which then the second best player which is Player B will score 3 points consistently until he benched on round 5 which then the next best player of C through Z will tag in so fort and so on in diminishing return-esque fashion

the result i was hoping is that as the formula is establish i can simply input the current round number and the excel will list the players with the highest score of the round along with their respective point. note that i also would like that it can display more than 1 set of result if multiple players of the given rounds have their points tied with each other

obviously at least to my limited knowledge, i need to use a variant of Excel MAX IF formula... but i cant wrap my head of the correct string i need to input. i hope Ablebits experts can help me with this and it will be very much appriciated

Hello!

You can get a list of values by condition using the FILTER function. Also try to use the recommendations described in this article: How to find top values in Excel with criteria.

I have a worksheet for a list of classes with a max seating of 8 per class. I am trying to find the simplest formula to calculate the total enrolled and max class size to fill session columns, in order (1-4), and then be able to AutoFill adjacent columns afterwards, if possible.

Example: TOTAL OF 4 SESSIONS

Max Class Size in A5 = 8

Total Enrolled in A6 = 27

Session 1 in A7

Session 2 in A8

Session 3 in A9

Session 4 in A10

Any help you can provide will be greatly appreciated.

Hello!

If I got you right, the formulas below will help you with your task:

A7 =

=MIN($A$5,$A$6-$A$5)

A8 and then copy it down along the column =

=MIN($A$5,$A$6-SUM($A$7:A7))

For more details read here: Excel MIN function - usage and formula examples.

Sorry for the confusion. I may have explained it wrong. Let me try again.

Max Class Size in A5 is 8

Total Enrolled in A6 is 27

All sessions are a total maximum of 8, so if session 1 reaches 8 but the total enrolled is 27, then A8 would then be populated up to 8 before going to A9, etc. Does that make more sense? Not sure if this is a MAX IF function.

I hope you can help. Thank you!

Hi!

If the formulas recommended to you are not what you need, explain why.

Hi All,

This platform look very amazing.

I need help with identifying the right formula for an array.

I have a table like this:

Task 18/05/2022 19/05/2022 20/05/2022 21/05/2022 22/05/2022

1 1

2 4

3 2

4 6

5 3

6 5

how would I search for the last entry made in this array and return the corresponding date, as the task are done in the sequential order.

Sorry It looks confusing, I thought it'll read blanks but it didn't.

task 1 was done on 18/05

task 2 was done on 20/05

task 3 & 4 was done 19/05

task 5 was done on 22/05

task 6 was done on 21/05

Hello!

To find the number of the last non-empty cell in a column, use the LOOKUP function. You can get the corresponding value from column B using the INDEX function.

=INDEX(B:B,LOOKUP(2,1/(A:A<>""),ROW(A:A)))

I hope I answered your question.

HI Alex,

thanks for the tip and it does help in a way. But I need a single formula that picks last non empty cell in multiple columns and rows, like you know searching through task1 to task6 and return the last date irrespective of the task name as reference.

I really appreciate your help

Hi!

The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one.

You can find the address of the last cell with data in the table using a VBA macro.

I am trying to get the lasted date of an activity, against an entrant name ?

In sheet 1 [summary] is a list of names (cells A2 to A49), cells B2 to B49 has a formula to count the number of activities for each entrant [ =COUNTIF(Activity!$D$2:$D$1037,A3) ] which works fine

In sheet 2 [activity] contains entries for each name col A date, col B class, col C type, col D name

On sheet 1 [summary] I am trying create a formula the get the last (highest) date for each name. I have tried " =MAX(IF(Activity!D2:D999=A3, Activity!A2:A999)) " but this returns 00/01/1900.

Can anyone suggest where I am going wrong?

Hello!

I'm assuming that you entered the formula as normal, not as an array formula. Press Ctrl + Shift + Enter so that array function works.

Also, you can use the MAXIFS function to find the maximum value with conditions.

Thanks Alexander, that works a treat. I have not used the array function before. Best wishes Joe

A B C D

Expenses Remaining Balance Limit

1 John 1000 9000.00 (he gets 1000) 10000

2 Mary 1500

3 Julie 2000

4 John 2500 6500 (he gets 2500)

5 John 1500 5000 (he gets 1500)

6. John 7000 5000 (he have to get 5000 only because his limit is 10000.)

The limit of each is Rs 10000.00. Please let me help to calculate the expenses of john not exceeding to Rs 10000.00.

=IF(B1<D1,B1,(SUMIF(A1:A6,A1,B1:B6)- D1))

5000 Should be resulted to be paid to John against his expenses not to exceed 10000

I have a table of chemical data. The main identifier is CAS#. I want to use the criteria of MAXIFS to sort out data flags, E and U AND I want to find the data value that has the latest sample date.

A B C D E F

CAS Agent Conc UOM Flags Sample Date

1 100-41-4 Ethyl Benzene 0.002 ppm 2/2/2016

2 100-41-4 Ethyl Benzene 0.002 ppm 1/27/2016

3 100-41-4 Ethyl Benzene 0.005 ppm 1/27/2016

4 100-41-4 Ethyl Benzene 0.002 ppm U 1/27/2016

5 100-41-4 Ethyl Benzene 0.002 ppm 2/2/2016

6 100-41-4 Ethyl Benzene 0.012 ppm E 2/3/2016

7 100-41-4 Ethyl Benzene 0.002 ppm 2/2/2016

8 100-41-4 Ethyl Benzene 0.002 ppm 2/10/2016

9 100-41-4 Ethyl Benzene 0.002 ppm Y 2/10/2016

Hi!

The MAXIFS function returns one maximum value and cannot be used to sort data. Try the SORT function.

I can't guess exactly what data you want to find. If it is a data row, then you can use the FILTER function. If it is a single value, then the INDEX+MATCH functions can be used.

For example,

=FILTER(A2:F10,F2:F10=MAX(F2:F10))

If this is not what you wanted, please describe the problem in more detail.

Thank you, that is pretty close, but not specific enough. Lets try this. I want a function that would return the values of 123 = 3, 987 = 7. The ID# is the unique identifier for each sample, need the most recent, largest value for each ID.

Column/Row A B C

1 ID# Value Date

2 123 5 2021-12-24

3 123 2 2022-03-25

4 123 3 2022-03-25

5 987 6 2021-12-24

6 987 7 2022-03-15

Hi!

The answer you received earlier is consistent with the information you provided. If you need to get only one value, you can use the INDEX+MATCH functions.

Replace the ID# number (987) in this formula with a reference to the desired cell.

=INDEX(B2:B6,MATCH(1, (MAX((A2:A6=987)*C2:C6)=C2:C6)* (MAX((MAX((A2:A6=987)*C2:C6)=C2:C6)*B2:B6)=B2:B6),0))

Here is the article that may be helpful to you: Excel INDEX MATCH with multiple criteria.

I have bitcoin daily data from year 2011 to 2022. I am trying to find the max price for a specific year, but when I use this formula =(MAX(IF(YEAR($A:$A=Q2),$C:$C,0)))

Column A has the dates

Column C has the prices

Column Q has the year eg. 2011

When I put the formula it shows value error. Is there anything I am doing wrong?

Hello!

To find the maximum value for a specific year, I recommend using the MAXIF function. All the necessary information is in the article above.

Sir

total electricity units 336

0-50 =7 rate

0-100 =8.50

101-300 =9.90

301-500=10.40

How to write if condition

Hello!

Read this guide for the answer to your question: Excel Nested IF statement: examples.

I hope my advice will help you solve your task.

Hi,

I have a list of dates and I have already found the formula for finding the latest date. But I would also like to have another condition where in another column if the value is not equalt to "ABC", then to exclude/skip it from the criteria.

Hi!

Have you tried the ways described in this blog post? Please re-check the article above since it covers your case.

We have 2 columns one is size and other is quantity. We want both that the multiples of the quantity are close to the maximum of a certain value

Like as

4600 4600

S,. No. Pcs Length (mm) Bar 1 Bar 2

1 312 2256 2

2 520 1506

3 208 1831

21 104 1506

31 312 2256 2

32 520 2256

33 208 2256

51 104 1356

Total 58 58

Stock 156 156

Hi!

I am not sure I fully understand what you mean.

Hello!

If i would like to know if its possible to do such search and look if the value is among the, say top 80% of the values. Something like the conditional formatting, but in an if statement (if that makes any sense :D ).

IF

MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)... is ...

among the top 80% of the values in the search criterias then print:

"this jump is within the top 80% of all jumps by females in round 3"

//Johan

Hello!

If I understand your task correctly, try the following formula:

=IF((RANK.EQ(A2,$A$2:$A$11,0)/COUNT($A$2:$A$11)) < = 0.8,"top 80%","")

You can learn more about RANK.EQ function in Excel in this article on our blog.

Hello!

Thansk alot, that's exactly what i was looking for.

Although, i found PERCENTRANK.EXC( ). This seems to work as well.

//Johan

Hello!

If i would like to make a similar solution but with instead use a weighted % value?

SAy,. if i have a total of 1200 points.

With the following distribution:

A: 543

B: 344

C: 100

D:90

E:50

F:22

G:14

H: 4

I: 1

A has 45,25% of the volume, B has 28,7% of the volume and C has 8,4% of the volume.

All these holds 82,35% and would be "top 80%"

Then D-G would be the next 15% and it would leave H and I in the bottom 5%.

Is there a way to get this info in any formula?

Kind regards

Johan

Hi!

I don't quite understand how you want to see the result. Try this formula and copy it down the column.

=SUM($A$2:A2)/SUM($A$2:$A$10)

Please have a look at this article: Excel Cumulative Sum - easy way to calculate running total.

If this is not what you wanted, please describe the problem in more detail.

Hi!

The thing is that i want to show this per row, something like an ABC grouping.

It could be done with descending sorting and just take total sum (0.8*1200=960, below that would be the "breaking point" to not be within the top 80%. But i want to show this per cell, without having to make a descending sorting and a fixed number of rows.

So something like:

if A, 543/1200 is among top 80% of the values --> "top 80% [True]

if B, 344/1200 is among top 80% of the values --> "top 80% [True]

if C, 100/1200 is among top 80% of the values --> "top 80% [False]

if D, 90/1200 is among top 80% of the values --> "top 80% [False]

and so on...

Top 2 values would hold 45,25+28,7 = 73,95% of the volume.

The next value will hold 8,4% of the volume and that would then be within the top 20%.

Im not sure if this is possible? I thought that the percentagerank.exk would do the trick, but this only returns the top 80 % of the values, not taking the actual weight on each row into the calculation.

Sorry if it got a little fuzzy... :D

Kind regards Johan

Hi!

Data in ABC grouping should always be sorted in descending order.

Try this formula:

=IF(OR(AND(SUM($A1:A$2)/SUM($A$2:$A$11)<0.8, SUM($A$2:A2)/SUM($A$2:$A$11)>0.8), SUM($A$2:A2)/SUM($A$2:$A$11)<0.8), "top 80% ","")

How to find the maximum value in one column but only if the number is integer.

For example I have in my columns standard values 1; 1.1; 1.2; 1.3; 2; 2.1 ;2.2. I want the function to return me in the next below cell 3 = which is maximum if only integer taken into account? Thanks!

Hello!

You can use the FILTER function to count only integers.

=MAX(FILTER(A1:A50,A1:A50=INT(A1:A50)))

I hope it’ll be helpful.

What is the formula to get min or max data shown below. Note dates with annex "A". Thank you

10-Oct-20 A

12-Oct-20 A

15-Oct-20 A

20-Oct-20 A

25-Oct-20 A

Hello!

I kindly ask you to have a closer look at the following paragraph of the article above - MAX IF formula with one condition.

I hope it’ll be helpful.

Great.. It works perfectly, the only issue is that the excel gets little slow but still I got what I was looking for. Thanks a lot..

I need to find second highest number in an array using logical not direct formula (large or small) that I know, please help:

Name Number

A 10

B 20

C 30

D 40

E 50

F 60

Like 50 is the second highest number in the above array but I need to find it with logic.

What if you want to take this concept one step further and find the person who made the highest jump, but is male. So using two sets of criteria

1. Male

2. Highest Jump

Return Value: Name

Hi Solomon,

Assuming the names are in column A, gender in column B, and the results in column D, this formula will work a treat:

=INDEX($A$2:$A$16, MATCH(MAX(IF($B$2:$B$16="male", $D$2:$D$16)), IF($B$2:$B$16="male", $D$2:$D$16), 0))

For the detailed explanation, please see How to find top values with criteria in Excel. The only difference is that this formula uses the MAX function to get the maximum value while the above linked example uses LARGE to extract top n values.

In Excel 365, you can use the FILTER function in conjunction with MAX or LARGE for the same purpose: FILTER top n values with criteria.

Thank you

It helps me. Thank you

YOU MAKE MY DAY, THANKS

I have not been able to find anything that addresses Max with one condition - where the target is not found. The Max condition is returning 0, as it should, but how do I change that 0 into a blank in the result cell.

My formula:

{=IFERROR(IF('Rep Visit Recap'!$K25>"",MAX(IF('Rep Visit Recap'!$K25='Checkin Data'!$P$2:$P$3815,'Checkin Data'!$A$2:$A$3815,"")),""),"")}

It's looking for a the last date if the account number is found. It works great except when there are no rows for that account number (I can't know this ahead of time, the checkin data are loaded into the workbook from another application).

How can I make the result date a blank, instead of a 0 when no rows are found for the account? I'm sure it's a simple think but I can't find any guidance for this.

Hi

I have a what is probably a silly question. I would like to maintain a record of a minimum value in a cell as the spreadsheet is updated through time. This requires an if statement to compare the current known low with the updated value. If the new update is below the current low then an update step should replace the current all time low with the new all time low. In my trials the circular nature of this algorithm causes issues.

Thanks

Hi Svetlana,

This article was really, really helpful! Thank you so much for walking through how this formula can be used in various combinations in such detail.

I'm currently working on a problem and am using one of these conditional functions to solve it, but it's giving me some unexpected results and for the life of me I can't figure out why it's going wrong.

I need to work out the MAX and MIN values for various samples but I want to exclude smaller data points - this is my criteria. I have to do this for 60 samples of varying size so I want to apply a consistent threshold across them all - I've decided to exclude data points that are below half the average for each sample.

The issue seems to be, when combining with INDEX MATCH I also need to apply the same criteria to the MATCH lookup array, otherwise it could return a result of one of the excluded data points that has the same max value (which in this case is a percentage so is a strong possibility).

This is the formula I'm playing with:

=INDEX(Full_sample[Country],MATCH(MAX(IF(Full_sample[# Requested]>(AVERAGE(Full_sample[# Requested])/2),Full_sample[Country Response rate])),((Full_sample[# Requested]>(AVERAGE(Full_sample[# Requested])/2))*(Full_sample[Country Response rate])),0))

Currently this formula indexes a country whose sample size is only 1, when half the average of the full sample is 12. When I break the formula down into constituent parts the MAX formula seems to work fine so I believe the issue lies with the MATCH lookup array and adding the AVERAGE calculation within it. The syntax of telling it to only look at response rates where the # requested > half the average seems correct, but I don't think it likes having that additional formula within the lookup array?

I've tried incorporating SUMPRODUCT within the MATCH and got slightly better results, but still incorrect. And strangely, when I alter the criteria to AVERAGE*2 or write in a hard number it seems to work correctly (maybe).

I've spent hours trying to solve this but am really stuck. I would very much appreciate your advice on how to incorporate this kind of criteria into the MATCH lookup array to make sure it is only indexing the values that correspond with the MAX criteria.

Thanks

[UPDATE]

I believe I have found a solution so wanted to post it before you spent any time on my above query. However, it seems a bit convoluted so I would still appreciate your advice on whether this is the best way to approach it or if you would recommend something else.

I ended up creating another nested IF within the lookup array where the TRUE value = 1 and match that to a criteria of 1. So rather than starting with the MAX (or MIN) as my match criteria, I incorporated my MAX and MINs into my IF lookup array. So far this appears to give me the correct result.

So the formula now is:

=INDEX(Full_sample[Country],MATCH(1,IF(Full_sample[# Requested]>(AVERAGE(Full_sample[# Requested])/2),IF(Full_sample[Country Response rate]=MIN(Country_Full_sample[F HQ Country Response rate]),1)),0))

I've used a MIN example here because it presents another issue, albeit one I'm prepared to live with. It could also happen in my MAX calculations but is less common.

The above formula returns a country whose response rate was 0, but at least where it's sample size was above half the average of the population (>12), whereas previously it was returning a country whose response rate was also 0 but sample size was only 1. However, there are 3 countries above the requested threshold who have response rates of 0. Ideally, I would like the indexed result to return the country who had the largest sample above the threshold whose response rate was 0 (in this case). This would tell me which country performed more objectively worse (or better in MAX cases), as it would take into account both the absolute size of the sample and the response rate.

Perhaps adding another ">" equation or incorporating the LARGE function may work, but I suspect this may make it more convoluted than is necessary. I've already set a minimum threshold so I could just as easily say that any country with the lowest response rate is the same as another.

Hello Dear

why ? My office can't do maxifs formula excel.

please you help me.

Thanks

Hello!

This feature is available on Windows or Mac if you have Office 2019, or if you have a Microsoft 365 subscription.

How about finding the 6 highest values in a whole table (both rows and columns)?

Hi Yza,

For this, you can use the LARGE function. Supposing your table is in A1:D20, enter the following formula in the topmost cell where you want the results to appear, and then drag it down through 5 more cells:

=LARGE(A1:D20, ROWS(A$2:A2))

The ROWS function is used to dynamically generate the k argument for LARGE, which determines the k-th highest value to return.

Could help me with this function I tried it many times and it doesn't work for me,I am using Exel 2013!

it always shows me the maximum value in $F$7:$F$16

Names Values "=MAX(IF($E$7:$E$16=E7,$F$7:$F$16))"

Ahmed 1 88

Ahmed 2 88

Ahmed 66 88

Tamer 1 88

Tamer 77 88

Khalid 1 88

Khalid 2 88

Rami 1 88

Rami 2 88

Rami 88 88

Hello!

I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?

Your data has 3 columns. Which ones do you use? What data is in E and F? What result do you get and what do you want to get?

The team member names are in E and the the team member totals are in F. They are trying to find the highest total for each of the team members individually, but the max if formula is only returning the highest total in F, which is 88.

I am having the same problem. I have have a list of 40k totals made up of 300 team members. I need to find the highest total for each individual team member, however the max if formula is giving me just the highest overall total in column F.

Any advice on how to have it only grab the highest total for an individual team member?

Hello!

If you have Office 2019, or if you have a Microsoft 365 subscription, use the formula

=MAXIFS(F:F,E:E,I1)

where I1 - name of the member

If you have an older version MS Office -

{=MAX(IF(E:E=I1,F:F))}

This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.

I hope it’ll be helpful.

Hi Svetlana,

I'm very thankful for this site & excel knowledge as it has developed my excel skills.

Now i want to know, how we can determine the top 3 Max values considering this MAX function??

From this example how i would get second 'MAX JUMP' value? by formula..

Hi Svetlana;

1 - I encounter, MAXIF is returning a zero date (00/00/1900) if the criteria not matched.

Is it possible to get #N/A if the criteria not matched?

2 - Is it possible to combine vlookup with MAXif?

Hi Raham,

1. That can be done by wrapping the IF function around your MAX IF formula and using the condition for the logical test. For example:

=IF(A2:A10=F1, MAX(IF(A2:A10=F1, C2:C10)), "N/A")

2. It is possible too. For example, the below formula finds the max value in A2:A10 based on condition (B2:B10=D2) and returns a match from column C:

=VLOOKUP(MAX(IF(B2:B10=D2, A2:A10)), A2:C10, 3, FALSE)

If you need to return values from left, then use INDEX MATCH instead of VLOOKUP as shown in this thread at stackoverflow.com.

Hi,

Thanks for this site which developed my basic formula skills and going on strongly.

I need to define a formula to identify the max of selected column (A1:A10) with 2 criteria, (B1:B10) shall have specific word 'B' (in D5) and (C1:C10) shall not be empty.

I've tried in 2 ways =MAXIFS(A1:A10,B1:B10,D5,C1:C10,""&"") and =MAX(IF((B1:B10=D5),(C1:C10""&""),A1:A10)) but both are showing error. Can you help me please?

Hi Selva,

Just use "<>" as the second criteria (non-empty):

=MAXIFS(A1:A10,B1:B10,D5,C1:C10,"<>")

Hello Selva!

If I understand your task correctly, the following formula should work for you:

=MAXIFS(A1:A10,B1:B10,D1,C1:C10,"<>"&D2)

where D2 is empty cell.

Hi..

I have a table of data which showing list purchasing items in our stock from 2004 to date,we wanted to get the latest date of purchased for each stock, i used MAXIF function and it worked, now i want te get the lastest supplier used for each of our stock item..we have approximately 5000 items . I tried to use MAXif functions but it fails,does it work only with criterias which involve numbers only......?

How would you find the highest result for each person?

Hi Tamsin Jessica,

Simply write a MAX IF formula for the first person as explained in this tutorial, and then copy the formula down by dragging the fill handle. Be sure to use a relative reference when referring to the cell containing the person's name for the reference to adjust correctly for each row.

Alternatively, you can make a list of unique names, write a formula for the first person, and then drag the formula down to get the highest result for each person.

The example only showing numbers. What if we want to reverse the result? IE from your example, who has the highest jump and on what round?

Hi Jackie,

For this, you can use the classic INDEX MATCH formula and nest a regular MAX function in the first argument of MATCH as the lookup value.

Assuming column A contains names, column B - rounds and column C - results, the formulas would go as follows:

The person who made the highest jump:

=INDEX($A$2:$A$10, MATCH(MAX($C$2:$C$10), $C$2:$C$10, 0))

On what round:

=INDEX($B$2:$B$10, MATCH(MAX($C$2:$C$10), $C$2:$C$10, 0))

Thanks, never thought of that. Ill try

What if you want to take this concept one step further and find the person who made the highest jump, but is male. So using two sets of criteria

1. Male

2. Highest Jump

Return Value: Name

Hi Jenny,

Assuming the names are in column A, gender in column B, and jump results in column D, you can use this formula:

=INDEX($A$2:$A$16, MATCH(MAX(IF($B$2:$B$16="male", $D$2:$D$16)), IF($B$2:$B$16="male", $D$2:$D$16), 0))

It is a simplified version of the formula to find top values with criteria. The difference is that here we use MAX IF to get the maximum value while the above linked example uses LARGE IF to filter top n values.

How can I print max absolute value irrespective of sign (positive or negative) and print max with sign.

Hi Rajesh,

The below example shows how to do both - get the maximum absolute value ignoring and preserving the sign: How to find absolute max value in Excel

Svetlana!!

One Caveat: Your all articles are very attractive. its consume my time eventhough i have learnt many..

Hi Imran,

If that's the only caveat, I think I can live with it :) And thank you for your comment, it really put a smile on my face.