Keeping score in a game and dealing with blank/non-numeric values

I’m trying to make an Excel scoresheet for a tabletop game.

There are TWO players (red and blue) that have NINE segments in each round to score. Each entry will have either one number, one or more letters, both, or nothing. These entries represent how many points are scored and what action took place.

Example 1: The scoresheet might look like this

I want the TOTAL column to keep a running total of the points scored for each player as the round progresses.

Example 2: If the above round has only gone as far as Segment 4, it’ll look like this

So far, I can extract a number from a single cell. The number will always be first and only one integer, so I can use =LEFT(B2,1) to get the number 1.

I can also string these together so =LEFT(B2,1) + LEFT(C2,1) + LEFT(D2,1) gives me 1 + 1 + 1 = 3.

However, if the field is blank (like F2:J2 in Example 2) or if it only contains a letter (like E2 and E3) I get a #VALUE! error. I think that makes sense because there’s no number. So how do I get around that? How do I get a running TOTAL if some entries are blank or don't contain a number?

I tried using =IFERROR(LEFT(A2,1),"0") + IFERROR(LEFT(B2,1),"0") + … to replace any error with the number 0, but I still get the #VALUE! error if the entry is blank or only contains a letter. The same thing happens if I remove the quotes =IFERROR(LEFT(A2,1),0) + IFERROR(LEFT(B2,1),0) + …

What else can I try?

The following formula is an array formula so depending on your version of excel, you may need to confirm your formula using <kbd>CONTROL</KBD>+<kbd>SHIFT</KBD>+<kbd>ENTER</KBD>. You will know you have done it right when {} show up in the formula bar around the formula. The {} may not be entered manually

=SUM(IF(ISNUMBER(--LEFT(B11:J11)),--LEFT(B11:J11),0))

Place the above formula in your Total cell. Adjust the range as required.

NOTE: when the number of letters for LEFT or RIGHT is not specified, it defaults to 1.

If you do not want to use the array formula you may enter each part of the formula 9 time changing the reference cell as you go.

Here are few alternative formulas using SUM() & IFERROR() with LEFT() and if you are using MS365 can use BYROW() function as well to return a dynamic spill array output.

First Alternative:

• Formula used in cell K2

=SUM(IFERROR(--LEFT(B2:J2),0))

Note:

• Since as per your post

> The number will always be first and only one integer, so I can use =LEFT(B2,1) to get the number 1.

Hence, we can use the LEFT() function by omitting the [num_chars] since if omitted it will be assumed as 1 so the function applied is

LEFT(B2:J2)

• Next, we applied double unary or double negative -- to do a math on the above and forces the results of all the string manipulation to number while the text will return error as #VALUE! to parse we use the 0 with IFERROR() function.

IFERROR(--LEFT(B2:J2),0)

• Lastly we wrap it within SUM() to get desired output.

=SUM(IFERROR(--LEFT(B2:J2),0))

• Also, it has to be noted if you are not using MS365 then while exiting the edit mode and using SUM() function you may need to hit the <kbd>CTRL</kbd>+<kbd>SHIFT</kbd>+<kbd>ENTER</kbd> based on Excel Versions however using SUMPRODUCT() function this can be avoided.

Second Alternative:

• Formula used in cell L2

=BYROW(B2:J2,LAMBDA(x,SUM(IFERROR(--LEFT(x),0))))

The above formula works in the same way only the difference is it uses the help of BYROW() function which applies a LAMBDA() function to each row in the array and results in per row as a single array formula. So that said it iterates each row one at a time to the LAMBDA() function which uses the SUM() function to return the total for each row.

It's pretty straight-forward to do this:

=SUM(IFERROR(VALUE(LEFT(B2:J2,1)),0))