How to count series of repetitions in column?

1. How to count series of repetitions in column?

Hello, I'm a beginner user and the problem is when I have a lot of data in the A column (2 possible values: "yes" or "no").
How I can count how many times value "no" is appeared in series?
The result can be stored in column C (see uploaded file).
For example:
yes
yes
no 1
yes
no
no
no 3
yes
yes
no
no 2
yes
etc.

Do I need to use array formulas or not?
What function can solve this kind of problems?
Excel file with example is uploaded.

2. Re: How to count series of repetitions in column?

Hi,
Use Countif:

``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``

3. Re: How to count series of repetitions in column?

Are you sure?
The result of =COUNTIF(A:A,"no") is number how many times "no" is appeared in column A ...

I need how many times "no" appears in one series
For example:
picture.PNG

4. Re: How to count series of repetitions in column?

This gets you close:

C1 =IF(A1="yes","",ROWS(A\$1:A1)-INDEX(MATCH(2,1/(A\$1:A1<>"no")),0))

5. Re: How to count series of repetitions in column?

This should do it:

C1 =IF(AND(A1="no",A2<>"no"),ROWS(A\$1:A1)-INDEX(MATCH(2,1/(A\$1:A1<>"no")),0),"")

6. Re: How to count series of repetitions in column?

Thank you, that second works completely.

7. Re: How to count series of repetitions in column?

You're welcome. Thanks for the rep!

8. Re: How to count series of repetitions in column?

*Deleted*

(I didn't read the question properly)

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