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

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

I have 2 columns which are designed to show whether a machine is switched on or off during a particular week during the year.

Column 1 is Week: 1, 2, 3, 4, 5, ...52
Column 2 is Value: On, On, On, On, Off, Off, Off, Off, On, On, On, On , Off, Off, Off, Off...

I want to have a cell to count how many times the machine is switched on every year, i.e. if it is on for 4 weeks and then off for 4 weeks, that would count as 1 time of being switched on. If it is on for one week and then switched off, that would also be swtiched on only once. Any ideas?

2. ## Re: 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

3. ## Re: Counting the number of entries in a column (but only once for consecutive entries)

Thanks for that, it's exactly what I was looking for. Could you also recommend a way to do a calculation which would differentiate between the duration of the time the machine was on. i.e. # of times that the machine was switched on for a period of less than two weeks, and # of times that the machine was switched on for a period of greater than 2 weeks?

4. ## Re: Counting the number of entries in a column (but only once for consecutive entries)

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))

5. ## Re: Counting the number of entries in a column (but only once for consecutive entries)

Thanks, it works

6. ## Re: Counting the number of entries in a column (but only once for consecutive entries)

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.

7. ## Re: Counting the number of entries in a column (but only once for consecutive entries)

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1