countif cell back to 0

[jarhead58]

How can I return my count to "0" after using this formula

=MOD(COUNTIFS(F13:T21,R2)+COUNTIFS(F13:T21,"Ground out")+COUNTIFS(F13:T21,"Fly out")+COUNTIFS(F13:T21,"Foul out")-1,3)+1

when F13:T21 are cleared? TIA

 

[Scott T]

I could not open the file. Since it can not be opened in Excel online it asked if I wanted to open it in Excel but it never opened.

What is in V2 of the score sheet?

For the formula to work what ever is in the "" needs to be in the cell so for this COUNTIFS(F13:T21,"DP") to work the text DP would need to be in the cell.

 

[jarhead58]

Try using this copy with the desktop version, I was able to open it with mine from onedrive. Let me know if you can. V2 has Fly Out in it, X2 has DP in it.

https://1drv.ms/x/s!Apokzs4VeX3NtkJNgka01SQx_J-2

 

[Scott T]

Still can not open it. I get

Reconnecting to Server As soon as the connection is restored, we'll Complete your last actions.

Do you have dropbox so I can just download the file without going though excel online.

 

[jarhead58]

I have it but never used it lol! Took me a few minutes to figure it out. Here's a link.

https://paper.dropbox.com/doc/Getti...GGOoJSTnnMgNEnF5uZ~LfAQ-4bt9o25jmtFKpag5VGZTQ

 

[Scott T]

Your code for a double play was overwriting the formula in M5 delete the last line line

Sub DP_Click()Worksheets("Score").Range("X2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'The below line is overwriting the formula in M5 delete it.
    'Range("M5").Value = Range("M5").Value + 2
    
End Sub

The formula I posted in post 12 works except that it will not zero out the outs until another out is entered. That is after 3 outs in the first the next batter in the 2nd hits a single the outs will still read 3 until someone records an out then it will read 1.

 

[jarhead58]

Your code for a double play was overwriting the formula in M5 delete the last line line

Sub DP_Click()Worksheets("Score").Range("X2").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    'The below line is overwriting the formula in M5 delete it.
    'Range("M5").Value = Range("M5").Value + 2
    
End Sub

The formula I posted in post 12 works except that it will not zero out the outs until another out is entered. That is after 3 outs in the first the next batter in the 2nd hits a single the outs will still read 3 until someone records an out then it will read 1.

The most part of it works except when an inning is started, I click on walk and it shows 3 outs. It looks like all of the hit's cause the counter go to 3 outs.

 

[Scott T]

To get it to reset on change of inning this formula will only count the current inning. To get this to work the formula needs to know the current inning I used an x above the inning. Your VBA can be modified to move the x after 3 outs.

=IF(COUNTA(F13:T21),COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),R2)+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"ground out")+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"fly 
out")+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"foul out")+(COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"DP")*2)+(COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"TP")*3),0)

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
11	x
12	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th
Score

 

[jarhead58]

To get it to reset on change of inning this formula will only count the current inning. To get this to work the formula needs to know the current inning I used an x above the inning. Your VBA can be modified to move the x after 3 outs.

=IF(COUNTA(F13:T21),COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),R2)+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"ground out")+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"fly 
out")+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"foul out")+(COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"DP")*2)+(COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"TP")*3),0)

	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
11	x
12	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Score​

Not quite sure Im following. Just replace my code in M5 with yours? I did that and it tells me there's a typo somewhere.

 

[Scott T]

If you do not put an x above the current inning the formula will give a N/A error since it is looking for the X.

 

[jarhead58]

I copied it into the M5 cell but it only took it as text. The = sign is there with no spaces before it. Not sure whats going on.