CODEWITHSUNDEEP
excel
encoreleeet
encoreleeet

Reputation: 374

count of substrings in column in excel

I want to count all series of same elements in column - take a look at example:

A 
B 
B 
B 
A
A
C
C
A
A

for data above, result should be 5 - explanation:

A ] 1 
-------
B ]
B ] 2
B ]
-------
A ]
A ]3
-------
C ]
C ]4
-------
A ]
A ]5

Upvotes: 1

Views: 78

Answers (2)

Abe Gold
Abe Gold

Reputation: 2347

If the series are in column A, for example from Row 1 to 15, use this formula:

=SUMPRODUCT(--(A1:A15<>A2:A16))

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

One way to get your count would be to use a formula which returns 1 whenever the value of a row changes. Assuming your letter data were in column A, beginning at row 1 (i.e. cell A1), I would enter the following formula into B2, and then copy it down the column:

= IF (A2 = A1, 0, 1)

Then just take the sum of the B column to get your answer.

Upvotes: 1

Related Questions