countif cell back to 0

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
280
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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)
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
280
That's it! Thank you! It was driving me nuts lol!

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)
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
280
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
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
280
Code:
[COLOR=#333333]=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)[/COLOR]
I've tried this thinking it would be as simple as that but it didn't work!
 
Last edited:

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
280
Anyone else like to help me out? Not sure what jtakw situation is! Any help would be appreciated! TIA
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,612
Office Version
365, 2016
Platform
Windows
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)
 

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
280
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?
 

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,612
Office Version
365, 2016
Platform
Windows
Could you give a sample of your data? What is in R2?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,282
Messages
5,485,849
Members
407,521
Latest member
marita

This Week's Hot Topics

Top