Solution to Count Before Change

Almir's picture

Solution to count before change at: http://excelexperts.com/data-counting

I guess that you want to count number of occurences of "Yes" and "No" in a block of consequtive occurences (i.e. before it changes from "Yes" to "No" or from "No" to "Yes"), right?

Having your data in column A, enter 1 in B2 and this formula in B3: =IF(A3=A2,B2+1,1).

Thus, when "Yes" turns into "No" or vice versa, counter resets to 1. If it is same as the row above, it increments counter to + 1.

Example file attached.

Please, let us know if this is what you need.

Count  before change
AttachmentSize
CountBeforeChange.xlsx8.51 KB