-2

I have some data spread over one column and 120 rows there is a repetition of the value 1 and 0 corresponding to certain operation mode

so it looks as per the image attached

My question is how to identify how many occurrences of the set 0 group in that column. The occurrence should be only if that group is preceded by 1.

How many occurrences of the set 1 group in that column if that is only followed by 0. And the counting of zeros shall be 1 for each set of group when it satisfied the condition in the associated column value. i.e. equal to or greater than a certain value in Column C. And the counting of ones groups shall be 1 for each set of group

I have added the full column image to see the whole picture, and now what I need is to adjust the equation, so that it will result in counting the groups of zeros, not the zeros it selves as that above equation resulted in; the equation result should be 4

One last question is that how to count how many cells in the zero set group, or the one set group, for each group; and how to count the last group of zeros as it is longer than the other groups?

enter image description here

Improve this question
2
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented May 25 at 22:20
  • You need to provide an example of your desired results.
    – Blindspots
    Commented Jun 3 at 20:06

1 Answer 1

Reset to default
0

If your data (0, 1) is in the range B1:B120, then the number of 0’s preceded by 1’s:

=COUNTIFS(B1:B119,1,B2:B120,0)

Number of 1’s preceded by 0’s:

=COUNTIFS(B1:B119,0,B2:B120,1)

You can add more conditions, keeping in mind that the lengths of the ranges in the formula must be the same.

After update it is still unclear whether only the first 0 after 1 is to satisfy the condition >=9, or all zeros, or any zero.
As I understood, it's about the first 0 and then there could be a formula:

=COUNTIFS($B$1:$B$6,1,$B$2:$B$7,0,$A$2:$A$7,">="&$C$1)

COUNTIFS

Improve this answer
5
  • thanks I have edited the question to be more clear, this is the first part works fine, but can not do the others, appreciate your inputs, thanks
    – Amr Ezzat
    Commented May 26 at 1:02
  • Thanks, it is about both zeros, the first zero, and also any other zeros in the column
    – Amr Ezzat
    Commented May 26 at 8:34
  • I have added the full column to see the whole picture, and what I need is to adjust the equation, so that it will result in count the groups of zeros, not the zeros it selves as that above equation resulted
    – Amr Ezzat
    Commented May 26 at 9:23
  • You didn’t show a range of cells of your data. Assuming the data is in the range A2:B121 and the constraint is in A1 (instead of col. C), the result 4 is given by a formula that does not take into account the constraint: =COUNTIFS(B2:B120,1,B3:B121,0) On the other hand, if we take into account the constraint and, as you wrote, all cells with zeros are supposed to meet it, then the result will be 0, because for each group of zeros in column B, at least one cell in column A does not meet the condition (in fact, the vast majority does not meet it).
    – MGonet
    Commented May 26 at 12:28
  • Of course, data should be numbers, not texts.
    – MGonet
    Commented May 26 at 12:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .