+ Reply to Thread
Results 1 to 6 of 6

Count and Lookup Help for a Leaderboard

  1. #1
    Registered User
    Join Date
    12-20-2007
    Location
    Staffordshire, England
    MS-Off Ver
    2007
    Posts
    48

    Count and Lookup Help for a Leaderboard

    Hi all,
    Think I may have bitten off more than I can chew in creating a SCRABBLE spreadsheet for my local running group. We are looking to do an event each week (create a new tab each week) and that's all good. But I want to create a leaderboard that tracks how many times someone has played and what there overall score is from the weeks they have played in.

    The Leaderboard tab is where I'm having issues. I will try as best to make my issues clear.

    Names - self explanatory
    Played - how many weeks they have participated. Can't figure out how to count check to see if Alex played in Week1, Week2 etc and tihs could run for 20 weeks!
    Points - so I've entered a vlookup to return the points for Week1 but there are errors (I've put these next to them) and now I don't know how to add Week2 scores to it

    Really sorry, probably as clear as mud but hopefully someone could help?
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count and Lookup Help for a Leaderboard

    Please try at
    B2
    =COUNT(Week1:Week2!AR3)

    C2
    =SUM(Week1:Week2!AR3)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-20-2007
    Location
    Staffordshire, England
    MS-Off Ver
    2007
    Posts
    48

    Re: Count and Lookup Help for a Leaderboard

    Hi,
    Thanks for that. I understand what you've done there, and it work to a degree but it will rely on the same players keeping the same row position each week? That may not be the case though if someone new joins or one week someone drops out. Sorry, I meant to add that the players won't be the same each week... So Alex is fine in the example as she's played weeks 1 and 2 but if she doesn't play in week3, someone else will be first in that list so AR3 will be returning someone else value? Sorry.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count and Lookup Help for a Leaderboard

    Ok, Please try at
    B2
    =SUMPRODUCT(COUNTIFS(INDIRECT("week"&{1,2}&"!AR2:AR99"),"<>",INDIRECT("week"&{1,2}&"!C2:C99"),A2))

    C2
    =SUMPRODUCT(SUMIFS(INDIRECT("week"&{1,2}&"!AR2:AR99"),INDIRECT("week"&{1,2}&"!C2:C99"),A2))

    for 20 week use row(a$1:a$20) instead of {1,2,…,19,20}
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-20-2007
    Location
    Staffordshire, England
    MS-Off Ver
    2007
    Posts
    48

    Re: Count and Lookup Help for a Leaderboard

    Hi,
    BRILLIANT... Thank you so much!! That does exactly as I want and will save me hours! Thanks again!

  6. #6
    Registered User
    Join Date
    12-20-2007
    Location
    Staffordshire, England
    MS-Off Ver
    2007
    Posts
    48

    Re: Count and Lookup Help for a Leaderboard

    So for peace of mind later, it will look like this:

    =SUMPRODUCT(COUNTIFS(INDIRECT("week"&{a$1:a$20}&"!AR2:AR99"),"<>",INDIRECT("week"&{a$1:a$20}&"!C2:C99"),A2))

+ 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. Leaderboard
    By kirbster123 in forum Excel General
    Replies: 2
    Last Post: 12-12-2018, 02:37 AM
  2. [SOLVED] Basic leaderboard
    By barber87 in forum Excel General
    Replies: 6
    Last Post: 05-19-2017, 11:25 AM
  3. Golf Leaderboard
    By plus4 in forum Excel General
    Replies: 4
    Last Post: 08-06-2014, 07:17 AM
  4. Making a leaderboard in excel
    By mmaher5 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-15-2012, 03:17 PM
  5. Leaderboard Problem
    By Nick Roydhouse in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2012, 01:20 AM
  6. Top 10 leaderboard
    By jlevs95 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2012, 03:05 PM
  7. Leaderboard
    By Augusta in forum Excel General
    Replies: 5
    Last Post: 12-28-2011, 10:01 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