+ Reply to Thread
Results 1 to 8 of 8

Rifle Club Scoring sheet - summing of numbers and letters

  1. #1
    Registered User
    Join Date
    02-19-2021
    Location
    Perth, Australia
    MS-Off Ver
    365
    Posts
    3

    Rifle Club Scoring sheet - summing of numbers and letters

    Hi Folks, my solution to this was so time consuming (lots of hidden columns, ISNUMBER and COUNTIF) that I thought I would post it and see if someone has a more elegant solution.

    Spreadsheet attached.

    Each shooter takes 10 shots
    The total is calculated by looking up values in the lookup table to provide a score out of 60.
    We then count the number of X's (Bulls eyes) and divide by 1000.
    Finally we add the two calculations together as the total.

    If am guessing maybe the solution will use SUMPRODUCT maybe, but I really have no idea.

    Help please
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,548

    Re: Rifle Club Scoring sheet - summing of numbers and letters

    EDIT
    Added a direct method, using sumproduct - see later ,

    if you need to show transparency and the values for the shots , i would make a seperate table with the values in

    AS the letter are 6 otherwise its the number in the cell
    then
    =IF(OR(E12="V",E12="X"),6,E12)
    copy across and down
    should convert into a number
    then the total for the shots
    Are a SUM of the cells , and then plus a countif of the X's
    so
    =SUM(Q12:Z12)+(COUNTIF(E12:N12,"X")/1000)

    copied down

    see attached

    the helper table could be in a different sheet

    Rather than use
    =IF(OR(E12="V",E12="X"),6,E12)
    a simpler formula
    test to see if the cell contains a number , of it does , then use that number , otherwise it must be a letter and the number 6
    =IF(ISNUMBER(E12),E12,6)

    OR direct calculated total
    Do the Values of X or V change
    a Hardcoded formula to work out the total in 1 go would be
    =SUMPRODUCT(--(NOT(ISNUMBER(E12:N12)))*6)+(COUNTIF(E12:N12,"X")/1000)+SUM(E12:N12)
    Attached Files Attached Files
    Last edited by etaf; 02-19-2021 at 08:05 PM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    02-19-2021
    Location
    Perth, Australia
    MS-Off Ver
    365
    Posts
    3

    Red face Re: Rifle Club Scoring sheet - summing of numbers and letters

    Thanks Etaf, I like the idea of the second table - I was just using hidden columns in amongst the scores.
    I am still hoping for a solution without a second table, but this solution will save me a lot of time.

    EDIT: I just saw that ETAF also gave me a second solution without the table using SUMPRODUCT. awesome!

    cheers
    Last edited by marc991; 02-20-2021 at 04:44 AM.

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,830

    Re: Rifle Club Scoring sheet - summing of numbers and letters

    If your Score/Value table will never change, you can eliminate it and just use this formula directly...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Alternately, this shorter array-entered** formula will also work directly without needing the Score/Value table...
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    **Confirm this latter formula using CTRL+SHIFT+ENTER and not just Enter by itself
    Last edited by Rick Rothstein; 02-19-2021 at 09:49 PM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Rifle Club Scoring sheet - summing of numbers and letters

    Rick - as the OP has 365, there is no need for array entry.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,830

    Re: Rifle Club Scoring sheet - summing of numbers and letters

    Ali - Yeah, I keep forgetting to check the OP's version (I'm working with an older version of Excel that requires array entry).

    Marc - This normally entered formula (for your version of Excel) is the one you should consider...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 02-20-2021 at 04:32 AM.

  7. #7
    Registered User
    Join Date
    02-19-2021
    Location
    Perth, Australia
    MS-Off Ver
    365
    Posts
    3

    Re: Rifle Club Scoring sheet - summing of numbers and letters

    Thanks Rick, that looks really simple and its working. The score table does slightly change for different classes of rifle, but I can adapt this formula easily.

    Cheers everyone

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Rifle Club Scoring sheet - summing of numbers and letters

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Summing Cells Containing Letters & Numbers
    By gtay82 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-04-2019, 01:40 PM
  2. Club database and scoring help & ideas
    By antonyg in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-05-2019, 10:56 AM
  3. Help with summing up a column of mixed numbers and letters
    By oputa ossai in forum Excel General
    Replies: 14
    Last Post: 05-24-2019, 04:17 PM
  4. [SOLVED] Summing letters that equal numbers
    By M. McGee in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-07-2013, 03:12 PM
  5. Replies: 3
    Last Post: 08-30-2010, 09:07 AM
  6. Replies: 1
    Last Post: 08-29-2010, 10:51 PM
  7. Replies: 1
    Last Post: 08-29-2010, 10:44 PM

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