Share via

Countif until value changes

Anonymous
2016-03-03T17:18:41+00:00

Hi,

How do you stop a countif when it reaches a certain value. For example, I have a column with a bunch of 0's and 1's. I want the countif to start counting at a particular "1" and stop counting once it reaches a "0". Can anyone help with this?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-03-03T17:44:11+00:00

The following is based on the column with 1s and 0s are in column A starting in A2

=COUNTIF(OFFSET(A2,0,0,MATCH(0,A2:A1048576,0),1),"<>0")

Was this answer helpful?

3 people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-03-03T21:52:54+00:00

    Hi,

    Thank you again for your answer. :-) I have another question, hopefully you can help.

    Here is an example, as you can see, the formula you gave me works, it is counting how many "Rule Resolved" (in another column) are located under "Resubmit" and placing the total on the "Resubmit" line (2.00). What I need to do now is divide the "0:32" by "2.00" and place those totals (16:00) on the "Rule Resolved" lines respectively while changing the "Resubmit" total from "0:32" to "0:00". Any ideas? Thanks!

    Resubmit 0:32 2.00
    Rule Resolved 0:00 0.00
    Rule Resolved 0:00 0.00

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-03-03T18:02:03+00:00

    Thank you, that did it! :-)

    Was this answer helpful?

    0 comments