+ Reply to Thread
Results 1 to 4 of 4

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

Hybrid View

  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
    FREQUENCY(IF(A5:Q5>0,COLUMN(A5:Q5)),IF(A5:Q5=0,COLUMN(A5:Q5)))
    . 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
    SUM(FREQUENCY(IF(A5:Q5>0,COLUMN(A5:Q5)),IF(A5:Q5=0,COLUMN(A5:Q5))))
    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
    =if(a5=1,or(if(offset(a5,0,-1)=1,1),offset(a5,0,1))*1,0)
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,370

    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    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