# "Count" from unspecified starting points in Google Sheets

I'm a baseball coach creating a Google Sheet to help track pitches and results for my college guys during games so they can see a lot of different information. See attached picture.

I'd like the chart to autofill the "count" column (Columns B and C). In the picture below I manually entered them, but would like them to autofill from the balls and strikes information in columns K and L. Not sure how to do that with unspecified starting and ending points. Also, the count will only ever be up to 3-2. Even if there is another foul ball, the count still stays at 3-2. After an end of the at bat "result" (such as "line hit" or "BB") is selected, I'd like the count to reset to "0-0" and a new "At Bat" is listed. Again, not at all certain if this is even possible without definite starting and ending points.

I could also reformat the cells to be a single cell and have it just be text that populates the cell. Not sure if that would be any easier/better to deal with only one cell instead of two.

• Can you share your spreadsheet and add some examples with what you would like the data to look like? For ex, list all of the possible ball strike combinations and the corresponding counts. I'm not too familiar with baseball, so the above would be helpful Commented May 5, 2021 at 20:20
• absolutely. Here is the link. docs.google.com/spreadsheets/d/… The following are all the possibilities (found in range AK42 through AK58). -Strike Options: Take Strike, Swing and Miss, Foul Ball, K, ꓘ, Ground Hit, Ground Out, Ground Error, Fly Hit, Fly out, Fly Error, Line Out, Line Error, Line Hit, Other Result. -Ball options: Ball, BB. Commented May 6, 2021 at 22:13

You want to maintain a running "Count" total

Balls (Column B)

• `=if(isnumber(\$A5),0,if(\$K4=1,if(and(\$B4=3,\$C4=2),3,B4+K4),B4+K4))`
• Enter in cell B5 and copy down for every pitch

Strikes (Column C)

• `=if(isnumber(\$A5),0,if(\$K4=1,if(and(\$B4=3,\$C4=2),2,C4+L4),C4+L4))`
• Enter in cell C5 and copy down for every pitch

Note: do NOT merge cells B4 and C4

LOGIC -

• Balls

• if the value in column A is a number then the count is 0,
else
• if previous pitch is a ball AND if the preceding row contains a "3-2" count, then the Ball count = 3
otherwise
• add the value in the preceding row of Column B to the previous pitch "Ball" value (Column K)
• Strikes

• if the value in column A is a number then the count is 0,
else
• if previous pitch is a ball AND if the preceding row contains a "3-2" count, then the Strike count = 2
otherwise
• add the value in the preceding row of Column C to the preceding pitch "Strike" value (Column L)

Sample

• Thank you, that mostly works. However, how can I set the maximum number of strikes to "2" even in different scenarios other than 3-2. I would like, using your example, column c to have a maximum value of 2, and column B to have a maximum value of 3, independent from the other. Commented Aug 17, 2021 at 21:35
• @Denny I'm sorry. I'm not a baseball player so I can't even visualise the scenario that you are trying to describe. I don't understand the difference between question and answer as written, and the new scenario. At a minimum, there needs to be more detail as well as an example of how a successful outcome might appear. If this answer satisfies the original question (as asked), then I suggest that you draft a new question. Also, you might consider accepting this answer. Commented Aug 19, 2021 at 1:01

That mostly worked. I added an extra if statement to keep 2 as the maximum number of strikes. See below:

``````=if(isnumber(\$A5),0,(if(\$C4=2,2,if(\$S4=1,if(and(\$B4=3,\$C4=2),2,C4+T4),C4+T4))))
``````