+ Reply to Thread
Results 1 to 23 of 23

MAXIFS with criteria

  1. 01-24-2017, 01:05 PM #1
    shiftyspina
    shiftyspina is offline
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    MAXIFS with criteria

    Hi

    I have a problem with a maxifs function and keep getting #NAME? returned

    My formula is

    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    In column J of the NZ MAXMIN sheet is the value, Column H is either 1 or 2, in the calculator d9 is either 1 or 2, in column A of the NZ MINMAX is a number between 1 and 52, in calculator d7 is between 1 and 52 and in d8 is between 1 and 52

    I want to find the max/highest number between the range set in cell d7 and d8 in the calculator, for example between 8 and 17, for product 1 which is the value in d9 but it keeps return #NAME?

    Any ideas what is wrong with this formula?

    Any help would be great, thanks
    Reply With QuoteRegister To Reply
  2. 01-24-2017, 01:10 PM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: MAXIFS with criteria

    MAXIFS is not a standard function in Excel (though it would be very useful if it did exist). Is this a UDF in your workbook?

    Pete
    Reply With QuoteRegister To Reply
  3. 01-24-2017, 01:35 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MAXIFS with criteria

    Quote Originally Posted by Pete_UK View Post
    MAXIFS is not a standard function in Excel (though it would be very useful if it did exist). Is this a UDF in your workbook?
    MINIF(S)/MAXIF(S) are new functions available in Excel 2016 (and maybe the online version O365 through subscription).
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 01-24-2017, 02:03 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    Quote Originally Posted by Tony Valko View Post
    MINIF(S)/MAXIF(S) are new functions available in Excel 2016 (and maybe the online version O365 through subscription).
    It is not in 2016 Pro version, it is in 365 and mobile
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 01-24-2017, 02:12 PM #5
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: MAXIFS with criteria

    The OP's profile states Excel 2010, so that is probably why s/he is getting the #NAME error.

    Pete
    Reply With QuoteRegister To Reply
  6. 01-24-2017, 02:13 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    Yup,that would be why. I am on 2016, and I get the #NAME error too
    Reply With QuoteRegister To Reply
  7. 01-24-2017, 02:20 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: MAXIFS with criteria

    Quote Originally Posted by FDibbins View Post
    Yup,that would be why. I am on 2016, and I get the #NAME error too
    Yet another good reason not to "upgrade", yet.
    Dave
    Reply With QuoteRegister To Reply
  8. 01-24-2017, 02:22 PM #8
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: MAXIFS with criteria

    Well, I'm lagging years behind, anyway, with XL2007 !!

    Pete
    Reply With QuoteRegister To Reply
  9. 01-24-2017, 02:22 PM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    Seems that MS is really heading in the subscription direction
    Reply With QuoteRegister To Reply
  10. 01-24-2017, 02:35 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: MAXIFS with criteria

    Open Source here I come!
    Reply With QuoteRegister To Reply
  11. 01-24-2017, 02:49 PM #11
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    It would really be a shame if they nickle and dimed excel out
    Reply With QuoteRegister To Reply
  12. 01-24-2017, 02:56 PM #12
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2502
    Posts
    13,700

    Re: MAXIFS with criteria

    Yup!___________
    Reply With QuoteRegister To Reply
  13. 01-24-2017, 03:05 PM #13
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: MAXIFS with criteria

    I am a subscriber of OFFICE 365 myself and I use Excel (currently 2016) Personal Edition. The yearly subscription cost is $69.99, which is $5.83 / month. I don't think this is too expensive to always have the latest Office edition. Updates are pushed on monthly bases and it takes just a couple of minutes for updates which occur monthly in the background. I am very pleased with how everything is working.
    Last edited by AlKey; 01-24-2017 at 03:08 PM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  14. 01-24-2017, 03:06 PM #14
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    My update is under $20
    Reply With QuoteRegister To Reply
  15. 01-24-2017, 03:35 PM #15
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MAXIFS with criteria

    Quote Originally Posted by FDibbins View Post
    It is not in 2016 Pro version, it is in 365 and mobile
    Quote Originally Posted by FDibbins View Post
    Yup,that would be why. I am on 2016, and I get the #NAME error too
    Hmmm...

    The very first sentence at this link says:

    Applies To: Excel 2016,...

    https://support.office.com/en-us/art...b-9b6376b28883

    Reply With QuoteRegister To Reply
  16. 01-24-2017, 03:41 PM #16
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    @ Tony, yes it does, but then it goes on to say...
    IMPORTANT: This function is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets.
    I have queried this before, and in another link, it said it does not appear in 2016 "standard" version
    Reply With QuoteRegister To Reply
  17. 01-24-2017, 03:41 PM #17
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    @ Tony, yes it does, but then it goes on to say...
    IMPORTANT: This function is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets.
    I have queried this before, and in another link, it said it does not appear in 2016 "standard" version
    Reply With QuoteRegister To Reply
  18. 01-24-2017, 03:42 PM #18
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    Yes, it does, but then it goes on to say...
    IMPORTANT: This function is available if you are an Office 365 subscriber and have the latest version of Office installed. It's also available with Excel Online, Excel Mobile, Excel for iOS, and Excel for Android phones and tablets.
    Reply With QuoteRegister To Reply
  19. 01-24-2017, 03:46 PM #19
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    wow holy duplicate posts, sorry
    Reply With QuoteRegister To Reply
  20. 01-24-2017, 03:48 PM #20
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MAXIFS with criteria

    I've lost all confidence in MS.

    They'er about as reliable as the owners of this site.
    Reply With QuoteRegister To Reply
  21. 01-24-2017, 03:49 PM #21
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: MAXIFS with criteria

    big money talks
    Reply With QuoteRegister To Reply
  22. 01-25-2017, 05:05 AM #22
    shiftyspina
    shiftyspina is offline
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Crayford, kent
    MS-Off Ver
    Excel 2013
    Posts
    394

    Re: MAXIFS with criteria

    Hi Sorry I am using excel 2016 hence the tag I put on the post, I need to update my profile, I haven't set it up as a udf, just thought it was standard in excel2016
    Reply With QuoteRegister To Reply
  23. 01-25-2017, 10:14 AM #23
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: MAXIFS with criteria

    Quote Originally Posted by shiftyspina View Post
    Hi Sorry I am using excel 2016 hence the tag I put on the post, I need to update my profile, I haven't set it up as a udf, just thought it was standard in excel2016
    You can always use an array formula which will work in any version of Excel.

    Array entered**:

    =MAX(IF('NZ MAXMIN'!H1:H100=Calculator!D9,IF('NZ MAXMIN'!A1:A100>=Calculator!D7,IF('NZ MAXMIN'!A1:A100<=Calculator!D8,'NZ MAXMIN'!J1:J100))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    You should avoid using entire columns as range references with array formulas. Use smaller specific ranges.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. MAXIFS with OR criteria
    By HalPlz in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-06-2019, 03:24 AM
  2. [SOLVED] Need result from Column A based on MAXIFS result from Column B
    By cchase22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2016, 06:15 PM
  3. [SOLVED] MaxIFs help
    By Hondahawkrider in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2016, 02:23 PM
  4. MaxIFS help
    By Hondahawkrider in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2016, 03:17 PM
  5. Minifs, maxifs, averageifs, medianifs, coeffvarifs
    By qwertyjjj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2014, 07:48 PM
  6. [SOLVED] Having issues writing a 'MAXIFS' array function
    By qaliq in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-23-2013, 11:01 AM
  7. finding the max value from an array produced from several criterias ("MAXIFS")
    By shay z in forum Excel General
    Replies: 3
    Last Post: 05-04-2010, 04:06 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1