Forum Discussion
Trivia Score Sheet
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"Ok, now on the formula, is there a way to do a mast input it in columns G, from rows 4 - 50
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.
- 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?
Yes, with new question it's better to start new conversation
SergeiBaklan 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.