+ Reply to Thread
Results 1 to 6 of 6

Evaluate 2 columns as conditions then return min and max of another column

  1. #1
    Registered User
    Join Date
    09-22-2014
    Location
    Tehran
    MS-Off Ver
    2013
    Posts
    11

    Evaluate 2 columns as conditions then return min and max of another column

    Hi,
    I need to have a solution to evaluate "Date" and "Card number" then return min and max of "Hour" of a specific card in a specific day.
    for example min hour of 1122 in 1 jun 2019 is 5:20 and max is 12:00.
    (the result could be in any format (table, Pivot, new column or ...)

    dataset is attached and is something like this:

    card number.jpg

    Many thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Evaluate 2 columns as conditions then return min and max of another column

    Try this:

    Excel 2016 (Windows) 32 bit
    E
    F
    2
    1122
    05:20:00
    3
    01-Jun
    Sheet: Sheet1

    =MINIFS($C$2:$C$15,$B$2:$B$15,E3,$A$2:$A$15,E2)

    or this:

    =MIN(IF($B$2:$B$15=E3,IF($A$2:$A$15=E2,$C$2:$C$15)))

    entered using CTRL+SHIFT+ENTER.

    Change MIN to MAX for the orher requirement.
    Last edited by AliGW; 10-30-2019 at 08:31 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Evaluate 2 columns as conditions then return min and max of another column

    Or (if your version of Excel does not support MINIFS):

    =AGGREGATE(14,6,$C$2:$C$15/(($A$2:$A$15=$E$2)*($B$2:$B$15=$E$3)),1)

    and

    =AGGREGATE(15,6,$C$2:$C$15/(($A$2:$A$15=$E$2)*($B$2:$B$15=$E$3)),1)

    No need for array entry.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Registered User
    Join Date
    09-22-2014
    Location
    Tehran
    MS-Off Ver
    2013
    Posts
    11

    Re: Evaluate 2 columns as conditions then return min and max of another column

    Thank you both
    I got it.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Evaluate 2 columns as conditions then return min and max of another column

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Evaluate 2 columns as conditions then return min and max of another column

    Ditto.

+ 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. Return sum of all columns that contain specified text and other conditions
    By bsommerhalder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-22-2018, 09:30 AM
  2. [SOLVED] Lookup the date and return the latest value under some conditions from multiple columns
    By PMH2384 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-09-2016, 03:31 AM
  3. [SOLVED] Evaluate to columns; compare two columns and return result
    By kjxavier in forum Excel General
    Replies: 2
    Last Post: 09-25-2014, 04:08 AM
  4. [SOLVED] IF formula, to evaluate two columns to return a new value
    By masond3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 02:08 AM
  5. VBA - Evaluate Conditions as String
    By Tejas.T in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 07:25 PM
  6. Replies: 3
    Last Post: 11-24-2011, 09:55 AM
  7. Return value in a column based on conditions matching with different column
    By sanits591 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2009, 01:28 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