Page 1 of 8 123 ... LastLast
Results 1 to 10 of 76

Thread: countif cell back to 0

  1. #1
    Board Regular
    Join Date
    Sep 2017
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Sep 2017
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: countif cell back to 0

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

    Quote Originally Posted by jtakw View Post
    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. #4
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: countif cell back to 0

    You're welcome, thanks for the feedback.

  5. #5
    Board Regular
    Join Date
    Sep 2017
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: countif cell back to 0

    Quote Originally Posted by jtakw View Post
    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. #6
    Board Regular
    Join Date
    Sep 2017
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    Last edited by jarhead58; Apr 12th, 2019 at 10:21 AM.

  7. #7
    Board Regular
    Join Date
    Sep 2017
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,525
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default 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)
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

  9. #9
    Board Regular
    Join Date
    Sep 2017
    Posts
    199
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: countif cell back to 0

    Quote Originally Posted by Scott T View Post
    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. #10
    Board Regular Scott T's Avatar
    Join Date
    Dec 2016
    Posts
    2,525
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: countif cell back to 0

    Could you give a sample of your data? What is in R2?
    Colonel Sandurz: Prepare ship for light speed.
    Dark Helmet: No, no, no, light speed is too slow.
    Colonel Sandurz: Light speed, too slow?
    Dark Helmet: Yes, we're gonna have to go right to ludicrous speed.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •