Counting the number of entries in a column (but only once for consecutive entries)

I would put in a helper column next to those that would make a "1" appear when the value to the left of it was "on" assuming the value to the left and up one row is not equal to "on".

So, for this example, assume we are in the 2nd row and the 1st, 2nd, 3rd columns are A,B, and C respectively.

Formula for C2 would be: =(B2="On")*(B1<>"On")

You should be able to paste that down to make a helper-column which you could get a sum from :D

Sure,

For the worksheet I attached earlier, this would give the count of "Times it was turned on for less than 2 weeks". -- (How many offs are there where the previous row contains a "new" On value)


=SUMPRODUCT((B2:B53="Off")*(OFFSET(C2:C53,-1,0)=1))

Does this do what you wanted...

Data Range

	A	B	C	D
1	Week	Status		On
2	1	On		3
3	2	On
4	3	Off
5	4	Off
6	5	On
7	6	On
8	7	Off
9	8	Off
10	9	Off
11	10	On
12	------	------	------	------

This array formula** entered in D2:

=SUM(IF(FREQUENCY(IF(B2:B11="On",ROW(B2:B11)),IF(B2:B11<>"On",ROW(B2:B11))),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Both formulas are array formulas**.

Originally Posted by 11416498

# of times that the machine was switched on for a period of less than two weeks

=SUM(IF(FREQUENCY(IF(B2:B11="On",ROW(B2:B11)),IF(B2:B11<>"On",ROW(B2:B11)))=1,1))

# of times that the machine was switched on for a period of greater than 2 weeks?

Is that supposed to be greater than or equal to 2 weeks?

Here's greater than 2 weeks:

=SUM(IF(FREQUENCY(IF(B2:B11="On",ROW(B2:B11)),IF(B2:B11<>"On",ROW(B2:B11)))>2,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.