+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    07-29-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    3

    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. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    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
    Attached Files Attached Files
    Last edited by GeneralDisarray; 07-29-2014 at 09:25 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  3. #3
    Registered User
    Join Date
    07-29-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    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. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    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. #5
    Registered User
    Join Date
    07-29-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    3

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

    Thanks, it works

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    Both formulas are array formulas**.

    Quote Originally Posted by 11416498 View Post
    # 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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counting Consecutive entries
    By AlexAgain in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-02-2014, 09:54 AM
  2. counting number of entries
    By onthecauseway in forum Excel General
    Replies: 1
    Last Post: 09-01-2011, 10:06 AM
  3. Counting the number of entries with 2 criteria
    By McQLon in forum Excel General
    Replies: 8
    Last Post: 02-11-2009, 09:16 AM
  4. counting the number of entries
    By Dan Mackman in forum Excel General
    Replies: 1
    Last Post: 11-10-2008, 10:42 AM
  5. Counting number of row entries
    By BobD in forum Excel General
    Replies: 1
    Last Post: 06-21-2006, 04:15 PM

Bookmarks

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