CODEWITHSUNDEEP
excelvba
user3682157
user3682157

Reputation: 1695

Find Minimum Value Based on 2 Criteria (Excel 2013)

Looking to find the max value in a column based on two sets of criteria

So the logic would be: Find the minimum value in column M, where the value in column A matches column N, and the value in Column Y is less than 318.

I've tried using an array formula like this but it doesn't seem to be working/is to memory heavy to run: =MIN(IF(AND(N:N=A2,Y:Y<=318),M:M))

is there a simpler way? or perhaps a UDF that could work?

Thank you for your help!

Upvotes: 0

Views: 1661

Answers (3)

barry houdini
barry houdini

Reputation: 46341

You can't use AND in these type of formulas because it only returns a single value rather than the required array.

Here are three possible working versions:

1.) Use * to simulate AND

=MIN(IF((N:N=A2)*(Y:Y<=318),M:M))

confirmed with CTRL+SHIFT+ENTER

2.) Use multiple nested IFs

=MIN(IF(N:N=A2,IF(Y:Y<=318,M:M)))

confirmed with CTRL+SHIFT+ENTER

3.) Use AGGREGATE function

=AGGREGATE(15,6,M:M/(N:N=A2)/(Y:Y<=318),1)

The advantages of this approach are that you don't need "array entry", and it can ignore any errors in the data

Either way it's best to reduce the ranges sizes if you can because it might be slow with whole columns

Upvotes: 1

Ditto
Ditto

Reputation: 3344

When things get this complex, I'll usually break it down and setup smaller/simpler formulas in seperate columns.

In other words, you have data in columns A through Y ?

So let's create a formula in column AA: 1) identify when value in Col A matches col N, and value in col Y < 318

=and(A1=N1,Y1<318)

2) copy AA1 to all the rows of your data.

3) now we have a condition to work off .. since there is a SUMIF and COUNTIF, but no MINIF .. we'll have to build that ourselves. first the IF: in column AB1:

=if(AA1,M1,"")

copy that down to all your data.

finally, do your min:

=MIN(AB:AB)

Should give you your answer.

You could probably splice the first two together, but again, building a complex formula like this, build it simply, first, ;)

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

Give this a try and adjust ranges to suit. Try not to use whole column references:

=SMALL(INDEX(($N$2:$N$101=A2)*($Y$2:$Y$101<=318)*$M$2:$M$101,),1+ROWS($M$2:$M$101)-COUNTIFS($N$2:$N$101,A2,$Y$2:$Y$101,"<=318"))

If you are using the whole column to pick up new data as it is added, consider using Dynamic Named Ranges instead

Upvotes: 0

Related Questions