Count Blocks of Data
 

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.

Count Blocks of Data
 

Brendan,

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

HTH,
Bernie
MS Excel MVP

"Brendan" wrote in message
...
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.

Count Blocks of Data
 

Post an example and the expected result

--
Biff
Microsoft Excel MVP


"Brendan" wrote in message
...
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.

Count Blocks of Data
 

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

"T. Valko" wrote:

Post an example and the expected result

--
Biff
Microsoft Excel MVP


"Brendan" wrote in message
...
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.

Count Blocks of Data
 

Wow - thanks - that works perfectly.

"Bernie Deitrick" wrote:

Brendan,

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

HTH,
Bernie
MS Excel MVP

"Brendan" wrote in message
...
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.