# countif cell back to 0

##### Active Member
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### jtakw

##### Well-known Member
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)

##### Active Member
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)

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

##### Active Member
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

##### Active Member
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:

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

#### Scott T

##### Well-known Member
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)``

##### Active Member
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
Could you give a sample of your data? What is in R2?

1,102,778
Messages
5,488,808
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...