# Thread: countif cell back to 0 Thanks:  23 Post #5151558 (1)Post #5261794 (1)Post #5260958 (1)Post #5260371 (1)Post #5262700 (1) Likes:  23 Post #5261603 (1)Post #5260926 (1)Post #5260348 (1)Post #5262621 (1)Post #5261506 (1)

1. ## countif cell back to 0

How can I return my count to "0" after using this formula
Code:
`=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

2. ## Re: countif cell back to 0

Hi,

Try this:

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

3. ## Re: countif cell back to 0

That's it! Thank you! It was driving me nuts lol!

Originally Posted by jtakw
Hi,

Try this:

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

4. ## Re: countif cell back to 0

You're welcome, thanks for the feedback.

5. ## Re: countif cell back to 0

Originally Posted by jtakw
Hi,

Try this:

=IF(COUNTA(F13:T21),MOD(COUNTIFS(F13:T21,R2)+COUNTIFS(F13:T21,"Ground out")+COUNTIFS(F13:T21,"Fly out")+COUNTIFS(F13:T21,"Foul out")-1,3)+1,0)
Hey jtakw,

I've tried getting this to work with another option but gave up. I forgot to add the option of DP(double play) +2 or TP(Triple play)+3. How would that work inside of this or is there another way to include those options? TIA

6. ## Re: countif cell back to 0

Code:
`=IF(COUNTA(F13:T21),MOD(COUNTIFS(F13:T21,R2)+COUNTIFS(F13:T21,"Ground out")+COUNTIFS(F13:T21,"Fly out")+COUNTIFS(F13:T21,"Foul out")-1,3)+1,0)+COUNTIFS(F13:T21"DP")-1,3)+2,0)+COUNTIFS(F13:T21,"TP")-1,3)+3,0)`
I've tried this thinking it would be as simple as that but it didn't work!

7. ## Re: countif cell back to 0

Anyone else like to help me out? Not sure what jtakw situation is! Any help would be appreciated! TIA

8. ## Re: countif cell back to 0

Is this what you want?
Code:
`=IF(COUNTA(F13:T21),MOD(COUNTIFS(F13:T21,R2)+COUNTIFS(F13:T21,"Ground out")+COUNTIFS(F13:T21,"Fly out")+COUNTIFS(F13:T21,"Foul out")-1+COUNTIFS(F13:T21,"DP")-1+COUNTIFS(F13:T21,"TP")-1,3)+1,0)`

9. ## Re: countif cell back to 0

Originally Posted by Scott T
Is this what you want?
Code:
`=IF(COUNTA(F13:T21),MOD(COUNTIFS(F13:T21,R2)+COUNTIFS(F13:T21,"Ground out")+COUNTIFS(F13:T21,"Fly out")+COUNTIFS(F13:T21,"Foul out")-1+COUNTIFS(F13:T21,"DP")-1+COUNTIFS(F13:T21,"TP")-1,3)+1,0)`
Not quite! I need it, as you can tell it's baseball, to reset to 0 if any of these countifs results in 3 outs. Say 1 batter fly's out, 1 out. Now if the next batter gets on base but the batter after him hits into a double play, the total becomes 3 outs and should reset the counter to '0'. The same with the triple play. 2 men reach base but the next batter hits into a triple play, rare I know but, 3 outs, reset counter to '0'. The code prior to the DP and TP work fine. It's just including the double play and triple play into the mix. Does that help?

10. ## Re: countif cell back to 0

Could you give a sample of your data? What is in R2?