Counting how many times a word is shown one after the other

[seriousdamage]

I have a file with one column and 2000 rows
In each sell there can be the word day or night
I would like to count how many times the word day shows after each other

As an example, if from A1 to A6 I always have Day, but in A7 I have night, the count is 6
If in A8 we find the word Day again, then the count starts again.

Many Thanks for any help
Regards
Nic

 

[FDibbins]

Not sure if this is what you want, but it does the count...

	A​	B​
2​	aa day	1​
3​	bb day	2​
4​	cc day	3​
5​	dd day	4​
6​	aa night	0​
7​	ee day	1​
8​	ff day	2​
9​	gg day	3​
10​	bb night	0​
11​	cc night	0​
12​	hh day	1​

B2=IF(ISNUMBER(SEARCH("day",A2)),B1+1,0)
copied down

 

[Eric W]

Are you looking for the longest consecutive run of the word "day" in a column? If so, try:

Excel 2012

	A	B	C
1	day		8
2	day
3	day
4	day
5	day
6	day
7	night
8	day
9	day
10	night
11	day
12	day
13	day
14	day
15	day
16	day
17	day
18	day
19	night
20	night
21	night
22	night
23	day
24	day
25	night

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula C1 {=MAX(FREQUENCY(IF(A1:A25="day",ROW(A1:A25)),IF(A1:A25<>"day",ROW(A1:A25))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[FDibbins]

I took the question to mean that "day" would be somewhere inside a string in a cell, but Eric showed a different (and possibly correct) view. In this case, my suggestion is modified to...

	D​	E​
2​	day	1​
3​	day	2​
4​	day	3​
5​	day	4​
6​	day	5​
7​	day	6​
8​	night	0​
9​	day	1​
10​	day	2​
11​	night	0​
12​	day	1​
13​	day	2​
14​	day	3​
15​	day	4​
16​	day	5​

E2=IF(D2="day",E1+1,0)
copied down