+ Reply to Thread
Results 1 to 4 of 4

MAXIFS formula that excludes based on date

  1. #1
    Registered User
    Join Date
    06-25-2015
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    4

    Question MAXIFS formula that excludes based on date

    I am trying to write a maxifs statement that will give me the greatest number for a person but only where the start date year of their number is less than 2020.
    So in the case, the maxifs formula should identify the number for Person1 to be 15, not 20. My current formula is MAXIFS(B:B,A:A,A1), which pulls in 20. I do not know how to tell the formula to ignore the row with the year 2020.

    Column A Column B Column C
    Person1 20 1/15/2020
    Person1 15 8/3/2019
    Person1 10 7/2/2017
    Person1 5 5/5/2015
    Person1 5 3/1/2011
    Last edited by xavior1325; 06-11-2019 at 10:52 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,706

    Re: MAXIFS formula that excludes based on date

    perhaps:

    =MAXIFS(B:B,A:A,A1,C:C,"<1/1/2020")

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: MAXIFS formula that excludes based on date

    Try this

    =AGGREGATE(14,6,$B$2:$B$6/(YEAR($C$2:$C$6)< 2020),1)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    06-25-2015
    Location
    Boston
    MS-Off Ver
    2016
    Posts
    4

    Re: MAXIFS formula that excludes based on date

    Thank you! This answered my question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUMIFS formula that excludes certain criteria
    By Murphy15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2018, 03:53 PM
  2. [SOLVED] I need a MAXIFS formula simalr to SUMIFS but with max value
    By Vicarious in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-25-2017, 04:50 PM
  3. Ranking Formula that Excludes Values
    By intercon in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2014, 06:11 PM
  4. [SOLVED] LEN formula excludes line feed?
    By Pavan Renjal in forum Excel Formulas & Functions
    Replies: 36
    Last Post: 01-07-2013, 08:04 AM
  5. MIN formula that excludes 0
    By albatross32 in forum Excel General
    Replies: 6
    Last Post: 08-14-2011, 09:38 AM
  6. Back-Dating to get an earlier date which excludes Holidays and Week-Offs
    By e4excel in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-06-2009, 11:27 AM
  7. [SOLVED] Minimum formula the excludes zeros
    By Joco in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2006, 03:55 AM

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