0

I'm interested in the number of groups of consecutive instances of a given value that occur in a column (a group being one or more consecutive instances of that value).

For the following example, the answer is 3:

x
o
x
x
o
o
o
x

For the example below, the answer would be 6:

o
x
x
x
x
x
o
o
o
x
o
x
o
x
x
o
o
o
o
x
x
o
x

The values are all in 1 column.

|improve this question
  • I'd personally use another column which checks the value and outputs '1' then sum them all but this looks like homework to me. – djsmiley2k - CoW Jul 23 '16 at 8:48
2

In B1 enter:

=IF(A1="x",1,0)

and in B2 enter:

=IF(AND(A2="x",A1<>"x"),1+MAX($B$1:B1),0)

and copy down. Finally in another cell enter:

=MAX(B:B)

enter image description here

|improve this answer
  • Just because: =SUMPRODUCT((A1:A22<>"x")*(A2:A23="x"))+(1*(A1="x")) 8P It built of yours so I will leave it here. – Scott Craner Jun 24 '16 at 22:48
  • @ScottCraner Thank you!........................ – Gary's Student Jun 24 '16 at 22:52
1

Data > Subtotal the column, Count, at Change In 'o' or 'x'

Do a Subtotal on the column of interest. It doesn't matter if the Subtotal assumes 'o' or 'x', either will suffice. After the subtotal is selected, choose, at change in 'o' or 'x' (it will depend on which is first in the column, but it's not important which). Then Count. So that's do Count at each Chang In '?'. Now that the subtotal is done, select branch '2' at the leftmost margin in Excel. At the bottom, beneath Grand Count, enter this formula:

=COUNTIF(A7:A35, "x Count") -- Where A7:A35 is the reference data/column

enter image description here

|improve this answer
0

B14 to B36 has this formula:

=IF(A14<>A15,IF(A14="x","isX",""))

B37 counts the isX:

=COUNTIF(B14:B36,"isX")

enter image description here

|improve this answer
  • I only get 2 when I use this on my first example. (Also, your edit of my question changed it to the point of seeming to be a different question.) – hBrent Jun 24 '16 at 16:11
  • 1) I tweaked the formula so it works if x is by itself in the first row. 2) I only rearranged your words so that it was presented in a more logical manner. I feel the layout change clarified your question without altering the intent and meaning. You're welcome to flag for moderator intervention if you disagree with my edit. – Sun Jun 24 '16 at 16:53
  • My concern was that the "number of X's that occur within a group" in your edit sounds like the number of times X appears within a group, not the number of groups of X. However, it seems that everyone who answered understood, so the edit didn't seem to cause a problem. I'll look at the updated answer. Thanks. – hBrent Jun 24 '16 at 17:12
  • That's true. It certainly was better than what was originally posted, but your later revisions make your question more clear. Thanks for that. – Sun Jun 24 '16 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.