# count a consecutive cells with text over different rows / columns

#### Ben9108

##### New Member
I would like to solicit your help to resolve the below excel issue.
1. Writing a formula to count consecutive cells with text over different rows/columns
2. The successful formula to count consecutive cells with text in single row/ column is shown below, how to write a formula to do the same thing over huge different rows/ columns?

W: Maximum consecutive cells counting from B3: R5
L: Maximum consecutive cells counting from B3: R5
E: Maximum consecutive cells counting from B3: R5
R: Maximum consecutive cells counting from B3: R5
S: Maximum consecutive cells counting from B3: R5
V: Maximum consecutive cells counting from B3: R5
N: Maximum consecutive cells counting from B3: R5

 B C D E F G H I J K L M N O P Q R 3 W W W @ L L L @ W W W W @ N N N N 4 E E E @ R R R @ E E W W @ L L L L 5 S S S @ V V V @ N N V W @ W S S S

<tbody>
</tbody>

Formula to count a consecutive cells with text in single row/ column =MAX(FREQUENCY(IF(B3:R3="W",COLUMN(B3:R3)),IF(B3:R3<>"W",COLUMN(B3:R3))))
Answer for the formula is 4

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### pgc01

##### MrExcel MVP
Hi

Do the lines wrap? Meaning, is row 4 the continuation of row 3 or each row should be processed individually?

Ex.:

Let's say that in your example B4="N".

In this case would we have

- 4 consecutive "N" in row 3 + 1 "N" in row 4

or

- 5 consecutive "N", because row 4 is the continuation of row 3

?

Replies
1
Views
453
Replies
0
Views
409
Replies
3
Views
476
Replies
4
Views
909
Replies
4
Views
424

1,191,358
Messages
5,986,187
Members
440,009
Latest member
VARGAST

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back