+ Reply to Thread
Results 1 to 6 of 6

Count number of '0's in a column, excluding successive repeats.

  1. #1
    Registered User
    Join Date
    01-23-2017
    Location
    Exeter, England
    MS-Off Ver
    Excel for Mac 15.27
    Posts
    3

    Count number of '0's in a column, excluding successive repeats.

    Hi,

    I joined this forum just to ask this question as I cannot find the answer elsewhere. I have a column of numbers (about 5000), and want to count all of the 0s, but count several in a row as one. I have used the COUNTIF function, however this counts several in a row as several.

    For instance, I want "0.4555, 0.6555, 0.7787, 0, 0, 0, 0, 0, 0.5666, 0.76665, 0.4434" to count as only one 0.

    To provide a bit of background, it's motion capture data, and I want to count the number of stops, and it captures at 120fps, so a stop normally leads to several 0s in a row, yet this should be counted as one stop.

    Thanks in advance for any help,

    Beaumy.

  2. #2
    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
    79,365

    Re: Count number of '0's in a column, excluding successive repeats.

    Maybe something like this?

    =COUNTIFS(A2:A7,0,A1:A6,"<>0")
    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.

  3. #3
    Forum Contributor
    Join Date
    10-30-2014
    Location
    England
    MS-Off Ver
    2007 / 365
    Posts
    279

    Re: Count number of '0's in a column, excluding successive repeats.

    Alternately, to mark each row for filtering...

    Please Login or Register  to view this content.
    Ensure that your data begins in row 2 (in the above code A1 will be the header/blank row above your data)

    This will give you a column of 1 and 0, where every first occurence of 0 will be marked with a 1 and everything else a 0. You can just filter or sum the column to get the total.
    Last edited by EchoPassenger; 01-23-2017 at 10:07 AM.

  4. #4
    Registered User
    Join Date
    01-23-2017
    Location
    Exeter, England
    MS-Off Ver
    Excel for Mac 15.27
    Posts
    3

    Re: Count number of '0's in a column, excluding successive repeats.

    Wow, thanks for the quick reply! The column of the numbers that I'm looking at is in Column L, and starts at L4 (due to lots of other stuff going on in the document), so I've put "=COUNTIFS(L4:L9000,0,L3:L8999,"<>0")" as the formula, which is of course based on yours but edited to try and match my cell location. Is that still right? I have no idea what this actually means though, as I'm new to Excel, would you mind explaining why it works please? Also, if I wanted to count anything close to 0, (ie >0.0009) instead, could I add that in easily?

    Thanks again and apologies for my Excel ignorance!

  5. #5
    Registered User
    Join Date
    01-23-2017
    Location
    Exeter, England
    MS-Off Ver
    Excel for Mac 15.27
    Posts
    3

    Re: Count number of '0's in a column, excluding successive repeats.

    Oh I think I've just understood what your code means, so it's just doing a Count if the one above it wasn't 0. Very clever. So could I just do "=COUNTIFS(L4:L9000,"<0.0009",L3:L8999,">0.0009")" to allow it to count everything close to 0 (all my data are positive values)? It seems to work and get a reasonable number so assume that works ok. Thank you both so much for your helpful and speedy replies!

  6. #6
    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
    79,365

    Re: Count number of '0's in a column, excluding successive repeats.

    You are welcome!

+ 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. VBA code for Count the number of string repeats
    By VINOTHBASKRAN in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-24-2014, 03:25 AM
  2. Count If Number Repeats in 5 Day Period
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-13-2013, 08:39 AM
  3. count the number of repeats
    By sweetness34 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2013, 03:59 PM
  4. [SOLVED] restricting column repeats to a certain number
    By hbangalore in forum Excel General
    Replies: 6
    Last Post: 03-26-2012, 07:10 PM
  5. Replies: 5
    Last Post: 02-03-2012, 07:05 AM
  6. [SOLVED] How can I count the number of times a letter repeats in a string?
    By Wiley in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2006, 02:00 PM
  7. [SOLVED] How can I count the number of repeats in a list of data?
    By SouthCarolina in forum Excel General
    Replies: 7
    Last Post: 03-07-2006, 06:10 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