+ Reply to Thread
Results 1 to 2 of 2

Counting of consecutive cells

Hybrid View

  1. #1
    Registered User
    Join Date
    02-08-2012
    Location
    Latvia
    MS-Off Ver
    Excel 2010
    Posts
    8

    Counting of consecutive cells

    Hi guys. I am just starting my way into depths of excel, and have encountered this problem.
    I need to count how many ranges of consecutive cells containing zero is there in a row, counting only the ranges that are at least 2 cells large.
    (EXAMPLE: 45757047400000000000456457457457000000000000000456450440 would return 2)
    I am bad with this, but from the forums I have managed to combine this array function:
    =COUNT(IF(FREQUENCY(IF(B1:B23=1,ROW(B1:B23)),IF(B1:B23<>1,ROW(B1:B23)))>1,1))
    It works if my data is written vertically, but I need to work with data written horizontally. I know this might sound stupid to you, but please bear with a noob

    What i have tried:
    =COUNT(IF(FREQUENCY(IF(B3:O3=0,COLUMN(B3:O3)),IF(B3:O3<>0,COLUMN(B3:O3)))>1,1))
    but this does not seem to return the right values.

    Any suggestions?

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,025

    Re: Counting of consecutive cells

    Can you upload example workbook and write desired output?
    Never use Merged Cells in Excel

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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