+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    02-15-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    7

    Cool 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. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    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))

    Edited to add:

    Sorry, being stupid. Try:

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

  3. #3
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    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))
    Click *, if my suggestion helps you. Have a good day!!

  4. #4
    Registered User
    Join Date
    02-15-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    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. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

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

    Quote Originally Posted by jraj1106 View Post
    =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)

    Quote Originally Posted by Andrew-R View Post
    .....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
    Audere est facere

  7. #7
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    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. #8
    Registered User
    Join Date
    02-15-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    7

    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:

    =IF(AND((F1="YES"),(N1="NO")),"GOOD","BAD")

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

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

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

    Try this standard formula
    Formula: copy to clipboard
    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.
    Last edited by Marcol; 01-02-2013 at 10:07 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  10. #10
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    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. #11
    Registered User
    Join Date
    02-15-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #12
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

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

    Can you post a sample of your workbook?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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