How do I count up the occurences of non continuous value,

I have a large dataset, that I have to check daily, and need to get two
separate results for.

1st requirement.

My data always starts in row 7. The amount of rows is variable, often
as high as 50000

In column “W” are the values “ON” / “OFF” or blank.

How can I count up the occurrences the word “ON” is there, and only
count each group of “ON” as 1 occurrence..

Eg
OFF, ON, OFF, ON, OFF, would count as 2
OFF, ON, ON, OFF, ON, OFF, would also count as 2
OFF, ON, ON, OFF, OFF, ON, ON, OFF, also = 2
OFF, ON, OFF, ON, ON, OFF, ON OFF, would count as 3

Sometimes there will also be empty cells scattered throughout the list
as well, these empty cells could be between the ON and wil not end that
particular occurence. OFF is the switch between start and end of each
occurrence

2nd requirement.

In column “D” is a timestamp, (row 7 being the earliest, row 8 the next
etc.). I need to know the total time that the value in column “W” was
“ON”.

Eg. The dataset may look like this

D W
6:17:47 OFF
6:17:49 ON
6:17:50 ON
6:17:52
6:17:53 OFF
6:17:54 OFF
6:17:59 ON
6:18:01
6:18:03 ON
6:18:04 ON
6:18:07 OFF
This would sum 12 Seconds -- 4 seconds on first occurrence, (6:17:49
to 6:17:53) and 8 seconds in the second occurrence (6:17:59 to
6:18:07).

As you can see, an empty cell should not have an impact on the result.

I know this sounds complex, but I am sure someone is able to assist.

Thanks in advance
Koala


--
koala
------------------------------------------------------------------------
koala's Profile: http://www.excelforum.com/member.php...o&userid=12953
View this thread: http://www.excelforum.com/showthread...hreadid=386389