countif cell back to 0

jarhead58

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

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
257
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
257
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
257
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
257
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,609
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
257
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,609
Office Version
365, 2016
Platform
Windows
Could you give a sample of your data? What is in R2?
 

Forum statistics

Threads
1,089,421
Messages
5,408,130
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top