# Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

1. ## Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

Hello Excelforum Community,
I've got a list of values, Yes and No, and I am trying to figure out a way to count how many times yes/no appears 2x, 3x,... 5x back to back. The only formulas i've found only count the total number of times a particular value appears. I'm wanting a formula that will count how many times a value appeared 2x, 3x, 4x, 5x.... etc. I included a mock up spreadsheet and colored coded it to help visually show what i'm am trying to accomplish.

Also, is there a technical term for what i am trying to do?

Thanks
-patron

1,000,000 Schrute Bucks to whoever can help me

2. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

perhaps with a helper column..

see attached

3. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

helper column?

4. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

Column B of the attachment in Post # 2 is the helper column which only populates number of consecutive yes/no at the last found instance (else it'll be blank)

Based on this column, results are populated in the grid D3:E6

Does this help?

5. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

just an update on what i'm trying to accomplish. I've got a list of 10,000 yes/no entries, and I want to know...
How many times 'Yes' appeared twice in a row
How many times 'Yes' appeared 3x in a row
How many times 'Yes' appeared 4x in a row
How many times 'Yes' appeared ...x in a row
How many times 'Yes' appeared 10x in a row

thanks to those helping

i see it now

8. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

I applied helper column to my spreadsheet and for the most part it works properly except for 2 areas. First, when the first 2 outcomes are not the same, the helper column does populate correctly. Also when I mess around with the first 3 outcomes "win loss win, win loss loss, or loss win loss" it gives me a Circular Reference Warning. idk what that means.

Second, populating the results of the helper column to grid F3:G16 are fine until it reaches 10 or more. Populating results for values 10 or more are inconsistent. I'm not sure where the results are coming from.

I've highlighted the problem areas. Also to make the outcome change between win/loss numbers<13=Loss and numbers≥13=win

Thanks
-patron

9. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

Ive changed the cell references to remove the cicular reference and also updated the grid.

See attached..

10. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

ace, thank you good sir, i will transfer 1,000,000 Schrute Bucks from my swedish bank account to ya.

11. ## Re: Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

this version is without helper columns for interest

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1