Finding minimum value in a column based on multiple text criteria in other columns

1. Finding minimum value in a column based on multiple text criteria in other columns

This formula allows me to find the lowest value in column U where column N contains the text "NO".

{=MIN(IF(\$N\$2:\$N\$10000="NO",\$U\$2:\$U\$10000))}

I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".

Any suggestions gratefully received.

Thank you.

2. Re: Finding minimum value in a column based on multiple text criteria in other columns

Try:

=MIN(INDEX((\$N\$2:\$N\$10000="NO")*(\$F\$2:\$F\$10000="YES")*\$U\$2:\$U\$10000,0))

Sorry, being stupid. Try:

{=MIN(If((\$N\$2:\$N\$10000="NO")*(\$F\$2:\$F\$10000="YES"),\$U\$2:\$U\$10000))}

3. Re: Finding minimum value in a column based on multiple text criteria in other columns

Hello

Try this:

=MIN(IF(AND(N2:N10000="NO",F2:F10000="YES"),U2:U10000))

4. Re: Finding minimum value in a column based on multiple text criteria in other columns

Thank you both.

Those should both work (I have entered them as arrays) but they are both returning 0. The answer should be much higher - there are no 0 entries in column U.

Any ideas?

5. Re: Finding minimum value in a column based on multiple text criteria in other columns

Are there any rows which meet the criteria? Is the value in column F exactly equal to "YES" (and not say "Yes", "yes", "YES " or "Y")?

6. Re: Finding minimum value in a column based on multiple text criteria in other columns

Originally Posted by jraj1106
=MIN(IF(AND(N2:N10000="NO",F2:F10000="YES"),U2:U10000))
AND returns a single value not an array so it won't be appropriate here, I recommend you use Andrew's second suggestion, (the first will return zero unless all rows meet the conditions)

Originally Posted by Andrew-R
.....Is the value in column F exactly equal to "YES" (and not say "Yes", "yes", "YES " or "Y")?
The formula isn't case-sensitive so as long as it's "yEs" the case won't matter

7. Re: Finding minimum value in a column based on multiple text criteria in other columns

Not case-sensitive? Well, every day's a school day, isn't it?

It will, however, be sensitive to leading or trailing spaces, so do check for those.

8. Re: Finding minimum value in a column based on multiple text criteria in other columns

I think this must be the problem.

When I add a column:

I get some "BAD"s even where F1="YES" and N1="NO". But I can't work out why!

9. Re: Finding minimum value in a column based on multiple text criteria in other columns

Try this standard formula
Formula:
`Please Login or Register  to view this content.`

[EDIT]
Where did all these posts come from ...
Last I saw was Post #4 only 5mins ago according to my machine.

10. Re: Finding minimum value in a column based on multiple text criteria in other columns

Try just =F1="YES" and copy down and see how many FALSE answers you get.

11. Re: Finding minimum value in a column based on multiple text criteria in other columns

It's very strange. I get all correct answers when I do =IF(F2="YES","CORRECT") and = (IF(N2="NO","CORRECT")

But not when I combine them!

12. Re: Finding minimum value in a column based on multiple text criteria in other columns

Can you post a sample of your workbook?

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1