+ Reply to Thread
Results 1 to 3 of 3

count non blanks and blanks frequency in chunks

  1. #1
    Registered User
    Join Date
    03-06-2019
    Location
    Tbay
    MS-Off Ver
    MS2016 desktop
    Posts
    2

    count non blanks and blanks frequency in chunks

    Hi,

    I am looking for help regarding the following calculations in MS Excel. I have a sample data arranged like this in a column

    variable name

    2
    3
    14
    4
    blank
    blank
    blank
    4
    1
    2
    2
    2
    blank
    blank
    1
    2
    31
    4
    blank
    1
    blank
    blank



    I am interested in summarised two variables:

    1) how can i get excel to count the number of columns in a data range that is present in a column continuously(numerical values) and print it in a separate column, for example, the output for sample data above would be
    4
    5
    4
    1

    2) similarly how can i count the number of chunks of "blanks" and print it a separate column: for example the output from the above data sample would be:

    3
    2
    1
    2



    Thanks for your help.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: count non blanks and blanks frequency in chunks

    one option:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    above would return the non-blank series' counts in the order in which they occur -- the same can be used for blanks by inverting the <>"" and ="" criteria.

    refer attached for working example
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-06-2019
    Location
    Tbay
    MS-Off Ver
    MS2016 desktop
    Posts
    2

    Re: count non blanks and blanks frequency in chunks

    Thank you very much! It works great for us! I really appreciate your time and effort

+ 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] Formula to count blanks and non-blanks with a dynamic range
    By brittdyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-12-2018, 07:45 AM
  2. [SOLVED] SUM/IF/FREQUENCY with blanks and duplicates
    By esbencito in forum Excel General
    Replies: 20
    Last Post: 03-27-2018, 06:18 AM
  3. BLANKS (Not Really Blanks) AND Rearrange ignoring blanks
    By shivspatil in forum Excel General
    Replies: 4
    Last Post: 02-02-2018, 08:28 AM
  4. [SOLVED] Access Query shows (Blanks) but no blanks in data!
    By MissDB in forum Access Tables & Databases
    Replies: 0
    Last Post: 09-08-2016, 07:16 AM
  5. VBA code finds blanks, but how to write VBA to ignore non blanks?
    By hopegriffin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2016, 10:51 AM
  6. [SOLVED] How to convert a horizontal vector with blanks into a vertical one without blanks
    By luv2glyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2014, 02:55 PM
  7. [SOLVED] Paste Special Skip Blanks not skipping blanks, but overwriting...
    By gsrosin in forum Excel General
    Replies: 0
    Last Post: 02-22-2005, 12:06 AM

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