+ Reply to Thread
Results 1 to 4 of 4

Formula to count only last x examples of an entry

  1. #1
    Registered User
    Join Date
    05-04-2020
    Location
    Leeds, UK
    MS-Off Ver
    2017
    Posts
    51

    Formula to count only last x examples of an entry

    Hi, I'm managing a sports database and one thing I want to do is create a table based on the last four matches between two players. I've got a formula that generates the matchup number between the players, and I thought a helper column stating whether it should be included or not would be good, but I'm not sure how to make it so it only counts the four largest numbers for a particular pairing.

    I've attached a sample worksheet that includes the formula mentioned, and what it should look like, like so:

    Capture.JPG

    Hope someone can help. Thanks for looking.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-04-2020
    Location
    Leeds, UK
    MS-Off Ver
    2017
    Posts
    51

    Re: Formula to count only last x examples of an entry

    I guess it's something like:

    if a is x and b is y and c is 4 or less less than the max c when x and y meet then "YES", otherwise "NO"?

  3. #3
    Registered User
    Join Date
    05-04-2020
    Location
    Leeds, UK
    MS-Off Ver
    2017
    Posts
    51

    Re: Formula to count only last x examples of an entry

    Oh! I got it:

    =IF(C2<(MAX((SUMPRODUCT(($A$1:$A32=$A2)*($B$1:$B32=$B2)))+(SUMPRODUCT(($A$1:$A32=$B2)*(B$1:$B32=$A2)))))-3,"NO","YES")

    Wasn't so hard as I thought.

  4. #4
    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,365

    Re: Formula to count only last x examples of an entry

    Congratulations!

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

    Thanks for sharing the solution.
    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.

+ 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 Formula examples/ tutorials
    By bfaws in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-29-2020, 11:35 AM
  2. formula to count line content but without duplicate entry
    By kangourou01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2017, 06:41 AM
  3. Creating Formula to Match Examples
    By quibilty in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-17-2015, 04:32 PM
  4. [SOLVED] formula to look for a number entry on one worksheet and return another fixed entry
    By kahuna130 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-14-2014, 04:15 PM
  5. Examples of Data Entry
    By Jonathan78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-27-2013, 06:54 AM
  6. Replies: 1
    Last Post: 08-22-2013, 05:11 PM
  7. [SOLVED] Looking for Userform examples - site on web, download examples
    By thom hoyle in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2005, 01:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1