Counting consecutive strings of text

[SamG]

Hi I want to count the number of consecutive strings of text in a column
where the text string will be indispersed by something like "NA":

Seagrass
Seagrass
Seagrass
Seagrass
NA
NA
Seagrass
Seagrass
...

I want to count the number of times Seagrass appears before a break of NA's
then count the next string of Seagrass. Presumably I would have a couple of
cells as a result (in this case 4 & 2)

 

[T. Valko]

Assuming that there will only be one break...in other words, you won't have
something like this:

seagrass
na
seagrass
seagrass
na
na
seagrass

Try these array formulas** :

For the number of times seagrass appears before a break of NA's...

=INDEX(FREQUENCY(IF(A2:A9="seagrass",ROW(A2:A9)),IF(A2:A9<>"seagrass",ROW(A2:A9))),1)

Note that if there isn't a break in the consecutive cells that contain
seagrass then this formula will return 0.

For the second run (or, in this case, the *last* run):

=LOOKUP(1E100,FREQUENCY(IF(A2:A9="seagrass",ROW(A2:A9)),IF(A2:A9<>"seagrass",ROW(A2:A9))))Both formulas need to be array entered.Array formulas need to be entered using the key combination ofCTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFTkey then hit ENTER.--BiffMicrosoft Excel MVP"SamG" <[email protected]> wrote in messageHi I want to count the number of consecutive strings of text in a column> where the text string will be indispersed by something like "NA":>> Seagrass> Seagrass> Seagrass> Seagrass> NA> NA> Seagrass> Seagrass> ..>> I want to count the number of times Seagrass appears before a break ofNA's> then count the next string of Seagrass. Presumably I would have a coupleof> cells as a result (in this case 4 & 2)

 

[Jacob Skaria]

Hi Sam

I am assuming you have only two text string in this column. Suppose you have
data as below starting from cell A1

ColA
Seagrass
Seagrass
Seagrass
Seagrass
NA
NA
NA
Seagrass
Seagrass
Seagrass
NA
Seagrass
Seagrass
Seagrass
NA
Seagrass
Seagrass
Seagrass
NA
Seagrass
Seagrass
NA


Try the below array formula and copy down as required...An array formula can
perform multiple calculations and then return either a single result or
multiple results. You create array formulas in the same way that you create
other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=SMALL(IF($A$1:$A$100<>$A$2:$A$101,ROW($A$1:$A$100)),ROW(A1)*2-1)-
IF(ROW(A1)=1,0,SMALL(IF($A$1:$A$100<>$A$2:$A$101,
ROW($A$1:$A$100)),ROW(A1)*2-2))

The result should be as below..

4
3
3
3
2
#NUM!
#NUM!
#NUM!

To handle the error returned try the below version (again array entered)

=IF(ISERROR(SMALL(IF($A$1:$A$100<>$A$2:$A$101,ROW($A$1:$A$100)),
ROW(A1)*2-1)),"",SMALL(IF($A$1:$A$100<>$A$2:$A$101,
ROW($A$1:$A$100)),ROW(A1)*2-1)-IF(ROW(A1)=1,0,
SMALL(IF($A$1:$A$100<>$A$2:$A$101,ROW($A$1:$A$100)),ROW(A1)*2-2)))