+ Reply to Thread
Results 1 to 4 of 4

Sum of consecutive number of cells that have same values in a row

  1. #1
    Registered User
    Join Date
    11-27-2019
    Location
    Lahore,Pakistan
    MS-Off Ver
    MS 16
    Posts
    2

    Sum of consecutive number of cells that have same values in a row

    I have around 1500 rows such as shown below. I want to sum the 1s that appear consecutively in this row.

    0 0 1 1 0 1 1 1 1 0 1 0 1 0 1 1 0

    As can be seen, the value 1 appears consecutively 2 times then 4 times and then finally 2 times. So, the output should be 2+4+2=8


    I have came close the solution by using
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    . Now, the only part left is conditional sum on this frequency formula array where the value is greater than 1.

    Simple sum on this frequency array i.e.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    gives me 10 as it takes into accout the two non-consecutive 1s in the row as well.

    Thanks.

  2. #2
    Valued Forum Contributor saravnepali's Avatar
    Join Date
    01-14-2019
    Location
    Sydney Australia
    MS-Off Ver
    2010
    Posts
    447

    Re: Sum of consecutive number of cells that have same values in a row

    Try this for A6 to Q6
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by saravnepali; 11-28-2019 at 06:37 PM.
    If you think someone helped you, click on the "* Add Reputation" as a way to say thank you.

    If your problem is solved, go to Thread Tools and select Mark This Thread Solved

  3. #3
    Registered User
    Join Date
    11-27-2019
    Location
    Lahore,Pakistan
    MS-Off Ver
    MS 16
    Posts
    2

    Re: Sum of consecutive number of cells that have same values in a row

    It's not working. Can you be little bit more explicit
    Last edited by AliGW; 11-30-2019 at 12:25 PM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Sum of consecutive number of cells that have same values in a row

    It is you who needs to be more explicit: explain exactly what you mean by "it's not working".

    Please provide a workbook - instructions in the yellow banner at the top.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ 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. [SOLVED] Asigning consecutive textbox values to multiple consecutive cells...
    By Hovoruha Octavian in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2019, 06:31 PM
  2. count the number of consecutive values >=2
    By jomuir in forum Excel General
    Replies: 8
    Last Post: 03-03-2017, 12:59 PM
  3. Counting the number of occurances with consecutive values above zero
    By bryan3228 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-13-2015, 09:35 AM
  4. [SOLVED] return the number of cells with the most consecutive specific values.
    By Bab1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-13-2014, 07:11 AM
  5. Replies: 17
    Last Post: 06-27-2014, 04:25 PM
  6. [SOLVED] How to get the number of times exactly 4 consecutive cells have a number greater than 6?
    By llane5150 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2013, 11:23 PM
  7. Resolved >>> Number of consecutive first values
    By owainl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-25-2007, 06:15 PM

Tags for this Thread

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