+ Reply to Thread
Results 1 to 9 of 9

Formula for Min Date that shows 5 times, with 2 conditions

Hybrid View

  1. 09-15-2022, 04:54 AM #1
    jjo23
    jjo23 is offline
    Registered User
    Join Date
    09-15-2022
    Location
    New Zealand
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    3

    Formula for Min Date that shows 5 times, with 2 conditions

    Hi Excel Forum,

    First time poster - as with a lot of questions, something similar has probably already been asked but could not find what I was looking for.

    I'm looking for a formula to give me the oldest date (column C), with conditions from columns L and O, that appear 5 times or more only.

    I had the below formula and then found it was picking up a few old outlier dates that are not relevant. I know I need a COUNTIF statement in here somewhere to achieve what I want.

    =MINIFS('Data'!C2:C99999,'Data'!L2:L99999,"=McDonalds", 'Data'!O2:O99999,"=Burger").

    Any help would be appreciated!
    Reply With QuoteRegister To Reply
  2. 09-15-2022, 05:04 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    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
    Reply With QuoteRegister To Reply
  3. 09-15-2022, 05:35 AM #3
    jjo23
    jjo23 is offline
    Registered User
    Join Date
    09-15-2022
    Location
    New Zealand
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    3

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    I have uploaded a rough sample sheet of what I want I want to achieve, hopefully its understandable
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 09-15-2022, 05:42 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,310

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    In D4 copied down:

    =IF(COUNTIFS($J$4:$J$18,B4,$K$4:$K$18,C4)>=5,MINIFS(I4:I18,J4:J18,B4,K4:K18,C4),0)

    or:

    =IF(COUNTIFS($J$4:$J$18,B4,$K$4:$K$18,C4)>=5,MINIFS(I4:I18,J4:J18,B4,K4:K18,C4),TEXT(0,"0"))
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 09-15-2022, 06:01 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    LoL. Sometimes I just get too complicated!!!


    =LET(OD,$I$4:$I$18,Ch,$J$4:$J$18,Pr,$K$4:$K$18,D,FILTER(OD,(Ch=B11)*(Pr=C11)),UD,UNIQUE(D),CUD,COUNTIFS(OD,UD,Ch,B11,Pr,C11),IFERROR(1/(1/MIN(IF(CUD>=5,UD))),""))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-15-2022, 06:02 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,310

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    @Glenn

    So tempting with all the new 365 functions, though!
    Reply With QuoteRegister To Reply
  7. 09-15-2022, 10:16 PM #7
    jjo23
    jjo23 is offline
    Registered User
    Join Date
    09-15-2022
    Location
    New Zealand
    MS-Off Ver
    Microsoft 365 Apps for Enterprise
    Posts
    3

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    Glenn your solution worked perfectly in the example book, however when I tried to replicate it to my workbook where it was needed, I was returning an error.

    I have uploaded one more sample workbook that is more true to the workbook and dataset that im working with. If further explanation or help can be provided

    Unfortunately AliGW I was still returning the oldest date with the formula you provided in both instances
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 09-16-2022, 04:50 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    1. The dates in columns C are NOT dates, they are date times.

    2. You (for some reason) put " " around some of the terms in my formula.

    3. Removing the " " was easy.

    4. However, the date-times did cause a few problems. I did get it working, but over 10,000 rows it became impossibly slow. So, as a workaround, I created a date-only helper column from your raw data. Copy it down as far as needed. It can be hidden, or on another sheet, or whatever, but it does keep performance acceptably high.

    5. I was only able to recreate 1 of your "expected" results - probably because of the size of the sample you provided.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 09-16-2022, 05:54 AM #9
    nick.williams
    nick.williams is offline
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    988

    Re: Formula for Min Date that shows 5 times, with 2 conditions

    How about:

    =LET(fdata,FILTER(INT(Data!C:C),(Data!L:L=M5)*(Data!O:O=N5)),IFERROR(INDEX(UNIQUE(fdata),MATCH(TRUE,FREQUENCY(fdata,fdata)>=5,0)),0))

    Doesn't seem too slow, although depends on the application.
    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. [SOLVED] Finding the number of times a date shows in a column for each date and entering values
    By ija34 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-27-2022, 03:33 PM
  2. [SOLVED] Sum times a given date shows on a new column, per date!
    By Aspone in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2020, 02:13 AM
  3. [SOLVED] formula for counting number of times a date shows in a cell that contains other info
    By Moffa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-27-2018, 02:38 AM
  4. [SOLVED] Formula for counting # of times 2 sets of data shows up
    By Statz in forum Excel General
    Replies: 3
    Last Post: 10-31-2016, 11:17 AM
  5. [SOLVED] A Formula Which Shows Values With Specified Column Conditions Met
    By zanshin777 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-26-2015, 04:02 PM
  6. Formula to show how many times a name shows up
    By Ajh in forum Excel General
    Replies: 1
    Last Post: 11-09-2010, 04:06 PM
  7. [SOLVED] How:iserror vlookup & count no. times value shows with conditions
    By Pauline in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-14-2005, 06:05 PM

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