Count Blocks of Data

[Brendan]

I have a list of data. Occasionally, there are periods where no data is
recorded and the string "NoData" appears.

I would like 2 single cell formulae that determine the number of times that
"NoData" appears and the number of blocks of "NoData".

The first is easily done with a counif statement, but the second is not as
easy. I have several long columns of data, so I'd rather not use a second
column for each column of data (it would greatly increase the size of the
spreadsheet). A single cell formula would be best. Is there such a formula?
The logic I would use is:

if the cell = "NoData" AND the previous (or following) cell <> "NoData" then
count it.

I can't figure out how to make that work. Any help would be greatly
appreciated.

 

[Bernie Deitrick]

Brendan,

=SUMPRODUCT((A2:A200="No Data")*(A1:A199<>"No Data"))

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Post an example and the expected result

 

[Brendan]

Bernie Deitrick solved it perfectly. Thanks for the help anyway.

 

[Brendan]

Wow - thanks - that works perfectly.

Brendan,

=SUMPRODUCT((A2:A200="No Data")*(A1:A199<>"No Data"))

HTH,
Bernie
MS Excel MVP