Google-sheets – “Count” from unspecified starting points in Google Sheets

googlegoogle 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.

Picture

Best Answer

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

Screenshot

Related Topic