+ Reply to Thread
Results 1 to 9 of 9

Countifs dates results past 7 days

  1. #1
    Registered User
    Join Date
    09-25-2018
    Location
    NJ, United States
    MS-Off Ver
    2016
    Posts
    18

    Countifs dates results past 7 days

    Good morning,

    I have a list of dates column B, and a list of outcomes in Column H. The outcomes include "win" and "loss".

    Can I get a win % result to always update for the past 7 days only...




    Thanks in advance!
    Last edited by JoeT15; 06-10-2019 at 12:08 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Countifs dates results past 7 days

    Sure. Try this:

    =COUNTIFS(H:H,"win",B:B,">="&TODAY()-7)/SUM(COUNTIFS(H:H,{"win","loss"},B:B,">="&TODAY()-7))

  3. #3
    Registered User
    Join Date
    09-25-2018
    Location
    NJ, United States
    MS-Off Ver
    2016
    Posts
    18

    Re: Countifs dates results past 7 days

    thank you, i'm getting a 100% result, but the last 7 days list the following:

    6/3 Win
    6/4 Win
    6/4 Loss
    6/4 Win
    6/6 Win
    6/6 Win
    6/6 Loss
    6/6 Loss
    6/7 Win
    6/7 Loss
    6/7 Win
    6/8 Loss
    6/8 Win
    6/8 Loss
    6/8 Tie
    6/9 Win
    6/9 Win
    6/9 Win
    6/9 Win
    6/9 Win
    6/9 Win
    6/9 Win
    6/9 Win

    Is it possible to factor out any "tie" results as well..
    Thanks for your help @63falcondude !

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Countifs dates results past 7 days

    Strange... I'm getting 73%. It already factors out ties.

    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-25-2018
    Location
    NJ, United States
    MS-Off Ver
    2016
    Posts
    18

    Re: Countifs dates results past 7 days

    hm okay, i see. It works in Excel, but when i upload through Google Sheets, it changes to 100% for some reason...

  6. #6
    Registered User
    Join Date
    09-25-2018
    Location
    NJ, United States
    MS-Off Ver
    2016
    Posts
    18

    Re: Countifs dates results past 7 days

    Screenshot attached

    Attachment 627737

  7. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Countifs dates results past 7 days

    Google Sheets and Excel are not the same... There was no mention that you were using a different software here.

    You can try this but I can't make any guarantee that it will work:

    =COUNTIFS(H:H,"win",B:B,">="&TODAY()-7)/COUNTIFS(H:H,"<>tie",B:B,">="&TODAY()-7)

  8. #8
    Registered User
    Join Date
    09-25-2018
    Location
    NJ, United States
    MS-Off Ver
    2016
    Posts
    18

    Re: Countifs dates results past 7 days

    sorry i should have mentioned it. Thank you so much for the help, this worked perfectly!!

  9. #9
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Countifs dates results past 7 days

    You're welcome. Happy to help.

    In the future, questions about other platforms should be posted here:
    https://www.excelforum.com/for-other...mobile-os-etc/

+ 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] COUNTIFS formula on past due Dates with conditions
    By rz6657 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-03-2018, 03:56 PM
  2. [SOLVED] Formula needed to compare number of days between dates but exclude negative results
    By bcmilne in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-20-2017, 10:23 AM
  3. Replies: 1
    Last Post: 07-20-2015, 11:40 AM
  4. Countifs and Sumifs of the past 30, 60, 90 days
    By asvallini in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2015, 03:12 PM
  5. conditionally format entire row for dates 15 days past today's date?
    By chalter in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2014, 11:46 AM
  6. Replies: 4
    Last Post: 05-15-2014, 04:39 PM
  7. Calculate Days Past in a Range of Dates
    By BrownBoy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2014, 10:56 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1