+ Reply to Thread
Results 1 to 9 of 9

Return MAX date based on two criterias

  1. #1
    Registered User
    Join Date
    08-30-2005
    Posts
    4

    Return MAX date based on two criterias

    Hi there,

    did my investigation, but not success so far. Here's the scenario:

    Both Col-A & Col-B can be filled in or not. If Col-B is filled, Col-A is as well.

    Within Col-C, I need to retrieve the MAX date from Col-A, for that related "string" (and not for the empty cell). To do so, I need obviously to exclude (ie: using isnumber) the empty fields...

    Col-A Col-B Col-C
    empty empty
    1.2.2014 string1 1.8.2016
    empty empty
    1.9.2015 string2 1.8.2018
    1.8.2018 string2 1.8.2018
    empty empty
    1.8.2016 string1 1.8.2016

    I tried, sumproduct in conjunction with max, but can only retrieve the row reference, not the value itself... otherwise, it returns #values...

    Any ideas from your expert views?

    Thanks in advance!

  2. #2
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Return MAX date based on two criterias

    Hi cedequ,
    Perhaps this

    Please Login or Register  to view this content.
    Control + Shift + Enter to Array it.
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++

  3. #3
    Registered User
    Join Date
    08-30-2005
    Posts
    4

    Re: Return MAX date based on two criterias

    Hi there,
    I already went through this solution, it works great (thank you), however I must avoid any array solutions (CES).
    Any alternate path to get this resolved, WITHOUT array type of solution?
    Thanks in advance for your support, much appreciated.


    Quote Originally Posted by wenqq3 View Post
    Hi cedequ,
    Perhaps this

    Please Login or Register  to view this content.
    Control + Shift + Enter to Array it.

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

    Re: Return MAX date based on two criterias

    Hi cedequ,

    Which version of Excel is this for?

    Regards
    Click * below if this answer helped

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

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Return MAX date based on two criterias

    Try

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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

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

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

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

    Re: Return MAX date based on two criterias

    Two non-array solutions:

    For 2010 or later:

    =AGGREGATE(14,,(B1:B100="string1")*A1:A100,1)

    For all versions:

    =MAX(INDEX((B1:B100="string1")*A1:A100,,))

    Regards

  7. #7
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Return MAX date based on two criterias

    I think Fotis1991's answer is that one you looking for.

  8. #8
    Registered User
    Join Date
    08-30-2005
    Posts
    4

    Re: Return MAX date based on two criterias

    And the winner is: XOR LX

    Thanks for suggesting the usage of aggregate (as I am using excel 2010), as this one is very handy to skip cell returning errors.

    I ended up, using the second parameter of aggregate (= 7), to discard errors:

    =AGGREGATE(14,7,(B1:B100="string1")*A1:A100,1)

    Other options listed above, requires that the scope does not contain empty / error cells, which I mentioned that I must address.

    May all of you (depending where you are), have a great week-end.

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Return MAX date based on two criterias

    Point is that you found your solution! So:

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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] Index / Match from another sheet based on 2 criterias (date & name)
    By chico.corrales in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2013, 10:30 PM
  2. Return a list using two criterias
    By vinceli in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-07-2013, 11:10 AM
  3. [SOLVED] Match 2 criterias and return a value
    By Spherous in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2012, 04:43 AM
  4. Replies: 4
    Last Post: 07-24-2008, 11:41 AM
  5. Compare Dates To Date Range & Return Date Based On Outcome
    By gusmontoya in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2008, 09:06 PM

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