+ Reply to Thread
Results 1 to 14 of 14

MaxIf without using an array formula

  1. #1
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    MaxIf without using an array formula

    On this forum (and elsewhere) I had only seen MaxIf when using an array formula:
    =Max(If(CriteriaColumn=Criteria,Values,0)) entered with Ctrl+Shift+Enter

    I basically stumbled on a way to get a maxif formula to work without array-entering it, by using Index:
    =Max(Index((CriteriaColumn=Criteria)*Values,))

    It returns the desired results; a maximum by criteria. Attached is an example workbook showing it in action.
    Attached Files Attached Files
    Last edited by tigeravatar; 03-02-2012 at 07:31 PM.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: MaxIf without using an array formula

    SUMPRODUCT can also be used in a similar way, i.e.

    =SUMPRODUCT(MAX((CriteriaColumn=Criteria)*Values))

    ...of course neither approach works for MIN because the multiplication of arrays means that you always get zero
    Audere est facere

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: MaxIf without using an array formula

    Yeah, I haven't been able to get a non-array MinIf to work
    Thanks for the sumproduct tip, though, I hadn't even considered doing it that way
    Last edited by tigeravatar; 03-02-2012 at 08:02 PM.

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: MaxIf without using an array formula

    Might not look right because it uses LARGE function, but this will give a "non array MINIF" assuming that value isn't negative

    =LARGE(INDEX((CriteriaColumn=Criteria)*Values,0),COUNTIF(CriteriaColumn,Criteria))

  5. #5
    Registered User
    Join Date
    03-07-2014
    Location
    Tulsa, OK
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: MaxIf without using an array formula

    Sorry to post on such an old thread, but should this formula work with other index formulas?

  6. #6
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,418

    Re: MaxIf without using an array formula

    another way to mimic non-array MINIF:

    Please Login or Register  to view this content.
    it will only work for values > 0.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    6,929

    Re: MaxIf without using an array formula

    Of course, with Excel 2010 we now have AGGREGATE, which will work for a range containing both negative and positive values:

    =AGGREGATE(15,6,1/(CriteriaColumn=Criteria)*Values,1)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,686

    Re: MaxIf without using an array formula

    Hi, and welcome to the forum.

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,891

    Re: MaxIf without using an array formula

    Here's a non-array MIN IF that handles zeros and/or negative numbers.

    Data Range
    A
    B
    C
    D
    E
    1
    Region
    Value
    ------
    Region
    Min
    2
    North
    -10
    North
    -10
    3
    North
    24
    East
    0
    4
    North
    37
    South
    21
    5
    East
    0
    West
    19
    6
    East
    14
    7
    East
    47
    8
    South
    21
    9
    South
    35
    10
    South
    69
    11
    West
    19
    12
    West
    43
    13
    West
    50


    Entered in E2 and copied down:

    =MIN(INDEX(((A$2:A$13=D2)*B$2:B$13)+((A$2:A$13<>D2)*1E100),0))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: MaxIf without using an array formula

    Based on Tigeravatar's formula in post #1, changed to give min instead of max:
    =MIN(INDEX(--SUBSTITUTE(--(A2:A13=A2),0,10^7)*C2:C13,))

  11. #11
    Registered User
    Join Date
    02-14-2016
    Location
    Australia
    MS-Off Ver
    10
    Posts
    1

    Re: MaxIf without using an array formula

    Quote Originally Posted by Tony Valko View Post
    Here's a non-array MIN IF that handles zeros and/or negative numbers.

    Data Range
    A
    B
    C
    D
    E
    1
    Region
    Value
    ------
    Region
    Min
    2
    North
    -10
    North
    -10
    3
    North
    24
    East
    0
    4
    North
    37
    South
    21
    5
    East
    0
    West
    19
    6
    East
    14
    7
    East
    47
    8
    South
    21
    9
    South
    35
    10
    South
    69
    11
    West
    19
    12
    West
    43
    13
    West
    50


    Entered in E2 and copied down:

    =MIN(INDEX(((A$2:A$13=D2)*B$2:B$13)+((A$2:A$13<>D2)*1E100),0))
    Hi Tony,

    Your formula works perfectly for me. Thanks. I had to register just to comment. Could you please shed some light on how the formula works? It was a bit over my head and I could not understand totally.

  12. #12
    Registered User
    Join Date
    11-21-2016
    Location
    Yorkshire
    MS-Off Ver
    2016
    Posts
    3

    Re: MaxIf without using an array formula

    Non-Array MaxIf/MinIf (Handles Negative and Non Whole Numbers)

    Minimum Number Greater Than 0 - =LARGE(AP3:AP9,COUNTIF(AP3:AP9,">0"))

    Minimum Number - Greater Than 15 - =SMALL(AP3:AP9,(COUNTIF(AP3:AP9,"<=15")+1))

    Maximum Number - Less Than 16 - =SMALL(AP3:AP9,COUNTIF(AP3:AP9,"<16"))

    If the text is required for the lowest number > 0 (See table below

    <-------AF-------><----AP---->
    3 Number One_______34
    4 Number Two_______23
    5 Number Three______15
    6 Number Four_______12
    7 Number Five_______21
    8 Number Six________0
    9 Number Seven______0

    This is the formula:
    =INDEX(AF3:AF9,MATCH(LARGE(AP3:AP9,COUNTIF(AP3:AP9,">0")),AP3:AP9,0)) --- Returns ( "Number Four")
    Attached Files Attached Files
    Last edited by philipswaby; 11-21-2016 at 12:40 PM. Reason: trying to add excel spreadsheet

  13. #13
    Registered User
    Join Date
    11-06-2019
    Location
    Manila, Philippines
    MS-Off Ver
    2016
    Posts
    1

    Re: MaxIf without using an array formula

    Quote Originally Posted by tigeravatar View Post
    On this forum (and elsewhere) I had only seen MaxIf when using an array formula:
    =Max(If(CriteriaColumn=Criteria,Values,0)) entered with Ctrl+Shift+Enter

    I basically stumbled on a way to get a maxif formula to work without array-entering it, by using Index:
    =Max(Index((CriteriaColumn=Criteria)*Values,))

    It returns the desired results; a maximum by criteria. Attached is an example workbook showing it in action.
    Hi,

    Thank you for the comment you provided. However, on my end, when I tried the formula, it provides 0 as a result. I hope you could enlighten me on this. Thank you.

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,178

    Re: MaxIf without using an array formula

    Administrative Note:

    Hello betina_mahs and Welcome to Excel Forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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