Trivia Score Sheet

[wyoung1969]

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?

[#2]

@wyoung1969 

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"

[#3]

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.

[#4]

@wyoung1969 

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.

[#5]

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

[#6]

@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"

[#7]

@Sergei Baklan The 1(1) you refer to is in when they bid the points but don't get the answer correct.  

The only time that the W would be used is when they don't know the answer and they want free points for that question.  

[#8]

Thank You for you help in this problem, Do I need to make another Post for another issue that is not related to this Trivia Score Sheet. It has to to do with another Work Sheets that have up to 6 work sheets?

[#9]

@wyoung1969 

Yes, with new question it's better to start new conversation

[#10]

Ok, now on the formula, is there a way to do a mast input it in columns G, from rows  4 - 50 

[#11]

@wyoung1969 

Copy/paste the formula into G4 and drag this cell down till G50. Or, after the formula is pasted, select G4:G50 and Ctrl+D.