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

 

[jarhead58]

Ok, I've setup F24:T32 the same as F13:T21. This original code works for the F13:T21,

=IF(COUNTA(F13:T21,F24:T32),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)+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"TO")+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"Fielders Choice")+COUNTIF(OFFSET(F13,0,MATCH("x",F11:T11,0)-1,9,1),"PO"),0)

but this doesn't for F24:T32

=IF(COUNTA(F13:T21,F24:T32),COUNTIF(OFFSET(F13(F24,0,MATCH("x",F11:T11,0)-1,9,1),R2))

. I only did it for the first check to see if it would work but it errors with "you've entered too few arguments". What am I missing?

 

[Scott T]

At first glance it looks like you are missing the Criteria for the COUNTIF and the Col for the OFFSET and a closing )

The 5 would be where the criteria for the COUNTIF goes and 0 would be where the COL for the OFFSET goes. Change to what you need

=IF(COUNTA(F13:T21,F24:T32),COUNTIF(OFFSET(F13(F24,0,MATCH("x",F11:T11,0)-1,9,1),R2,[COLOR=#ff8c00]0[/COLOR]),[COLOR=#ff0000]5[/COLOR]))

I would probable just modify the existing formula by increasing the range from F13:T21 to F13:T32.

 

[jarhead58]

At first glance it looks like you are missing the Criteria for the COUNTIF and the Col for the OFFSET and a closing )

The 5 would be where the criteria for the COUNTIF goes and 0 would be where the COL for the OFFSET goes. Change to what you need

=IF(COUNTA(F13:T21,F24:T32),COUNTIF(OFFSET(F13(F24,0,MATCH("x",F11:T11,0)-1,9,1),R2,[COLOR=#ff8c00]0[/COLOR]),[COLOR=#ff0000]5[/COLOR]))

I would probable just modify the existing formula by increasing the range from F13:T21 to F13:T32.

Ok, did the simpler, F13:T32 but it doesn't produce an out in the "Out" cell when I use one of the "Out" buttons; Ground Out, etc. in the F24:T32 area.

 

[jarhead58]

Got it! Had to increase the Col height from 9 to 20 to accomodate for the next section.

 

[Scott T]

Yes, you will want to go though all your formulas and code to make sure you pick up the new range.

 

[jarhead58]

Here is a link for the latest file. https://1drv.ms/x/s!Apokzs4VeX3NtkkSBFXa9k_P3T4w

I have a "W" in F13, an "X" in F11, a base hit in F14 and M5 >= "0". If the base hit player is picked off, I use the "PO" button in AA1 to replace the base hit in F14. The only thing that I can't seem to get working is incrementing the number in M5 to register the out. Here is what I am using.

If Range("F11:T11").Text = "x" And Range("F13:T32").Text = "W" And Range("M5").Value >= "0" Then    Range("M5").Value = Range("M5").Value + 1
             Else
    End If

 

[Scott T]

Can you put the file in Dropbox.

Also in proper scoring in baseball you do not want to remove the bit from the record. You would still want to record the hit then record that they got picked off. This is one of the problems of trying to do this in Excel.

 

[jarhead58]

Can you put the file in Dropbox.

Forgot about the issue with Onedrive. https://www.dropbox.com/s/ve0tbobgidxrevc/Scorecardtemp (1).xlsm?dl=0

 

[jarhead58]

Can you put the file in Dropbox.

Also in proper scoring in baseball you do not want to remove the bit from the record. You would still want to record the hit then record that they got picked off. This is one of the problems of trying to do this in Excel.

That actually was working prior to new coding.

 

[Scott T]

When you press the button you are copying cell AA2. This puts a 1 in the cell and a picture. The outs formula is looking for "PO" so it does not pickup the pick off since the cell has a 1 in it. Change AA2 to PO and it should work. Cell Z2 for TO has the same problem.