+ Reply to Thread
Results 1 to 2 of 2

find mins/maxes with several conditions (similar to averageifs)

  1. #1
    Registered User
    Join Date
    11-12-2012
    Location
    Seattle
    MS-Off Ver
    Excel 2010
    Posts
    1

    find mins/maxes with several conditions (similar to averageifs)

    I've seen a few threads describing how to create your own MINIF function, but none that address the situation where several conditions need to be met (MINIFS). I want my formula to search a different sheet, 'Soil Results,' and calculate the smallest of the set where column D says '1', column G says 'subsurface soil,' and column I says 'cadmium.' The numbers I want to look at are in column J. I'm currently trying to use an and statement nested inside an if statement, but I think I must have the syntax wrong somewhere. I have the following formula, which is just returning 0. Any help would be greatly appreciated!

    =MIN(IF(AND('Soil Results'!D2:D2761="1",'Soil Results'!G2:G2761="subsurface soil",'Soil Results'!I2:I2761="Cadmium"),'Soil Results'!J2:J2761,5))

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: find mins/maxes with several conditions (similar to averageifs)

    something like this array formula

    =MIN(IF(D2:D10=1,IF(G2:G10="sub",IF(I2:I10="cad",J2:J10))),5) -ctrl+shift+enter ->change the references.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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