Forum Discussion
Trivia Score Sheet
What I'm trying to accomplish is having the letter w to represent a word, but also having a numerical value with it like 6w But here is the tricky part of it, I need to have that numerical value to be add with the rest of the row. To give me the total sum. How do I do this?
10 Replies
Never deal with Trivia. Yes, it's better to separate but if combined value is the rule of the game it's important to know what is the logic behind such combination. If always only one letter after the number like here
we may sum numbers with
=SUMPRODUCT((RIGHT($B$2:$G$2)=$A5)*LEFT($B$2:$G$2,LEN($B$2:$G$2)-1))For other logic it could be another approach.
The Idea behind this: Is there is a Word for the day, that if the team goes to the website and get the Word for the day. Then they can use this word for a Mulligan for a question that they don't know the answer to and get the points that they bet. It can be used only 1 time for the entire of the Game. So each team has 1 Free pass or Mulligan for game night.
There can be up to 50 teams, and there are 6 rounds with 3 questions per round, not including Halftime and Wager Question and sometimes Bonus Question.
I am trying to label the points numerical value with a w to let me know that they used their word for the day. Also, having the that Cell Turn Yellow, after I hit Tab or Enter.
Now having said that, I need the points (numbers) for each to team to be added up in the sub total cell
Please see the table below
Team Names Question 1 Question 2 Question 3 Sub Total Just guessing 1 3 5W 9 Britganders 5 3 1(1) 8 Scooby 3 1w 5 9 wyoung1969 , thank you.
It's unclear what to do if you have any other text after the number except w. Like in case of 1(1). If ignore as in the sample
formula could be
=SUMPRODUCT(1*IFERROR(SUBSTITUTE(LOWER(C4:E4),"w","")+0,0))You may also highlight cells with w by conditional formatting rule with formula
=RIGHT(C4)="w"
Hello wyoung1969,
I would have to agree with mathetes. Perhaps you should rethink your method. Is there a reason that the numbers must be concatenated with the letters? Having numbers and text in separate columns would make this task much easier.
Maybe you could supply a sample file to help illustrate what exactly it is that you are trying to accomplish and why this is the method you are choosing.
Is your numerical value always a single digit?
Is your letter always a single letter?
Are they always sequenced NumberLetter, as in "6w" or are they sometimes "w6"?
How many columns of this kind of entry are in the row that you want to add up?
If it's always in the "6w" format (single digit, single letter) this formula =VALUE(LEFT(D3,1)) will return the value of the digit and could be used, in conjunction with others like it, =VALUE(LEFT(D3,1))+VALUE(LEFT(D4,1))+VALUE(LEFT(D5,1)).....
If it's less predictable or less consistent stuff you'll be dealing with, you'd probably be better off re-thinking the way you enter the data. Why not have separate cells for the numbers and the letters. Easy to add the numbers, and, frankly, easier to concatenate into the combinations, if that's really needed.
In other words, there's more to be defined here before we come up with a single "best solution"
