CODEWITHSUNDEEP
excelfunctioncountif
Michel Daher
Michel Daher

Reputation: 1

how many times a value occurred consecutively in a column- excel

I have been searching for a while and can't seem to find an answer so here I am posting hoping someone might help. I am trying to find how many times a value occurred consecutively in a column. For example, how many time did the word " Negative" occur 4 time consecutively in the column. How many times did it occur 5 times in a row ( I mean by in a row,one after another) I have attached a picture Thank you enter image description here

Upvotes: 0

Views: 1116

Answers (1)

nightcrawler23
nightcrawler23

Reputation: 2066

Assuming your datais in column B. In column C enter this formula as an array

SUMPRODUCT(IF(ISERROR(OFFSET(B7,0,0,$C$1,1)),0,IF(OFFSET(B7,0,0,$C$1,1)="Negative",1,0)))=$C$1

where $C$1 has the value of number of consecutive "Negative"s to be found. To enter the formula as an array, enter the formula in the cell, then press CtrlShiftEnter. You can then drag this all the way down.

us this formula in another cell to get the number of occurences of consecutive Negatives

=COUNTIF(C2:C1088,TRUE)

Here is a Google sheet with Demo

Upvotes: 0

Related Questions