+ Reply to Thread
Results 1 to 5 of 5

Formula to count number of visible rows, and formula to count visible blanks

  1. #1
    Registered User
    Join Date
    05-28-2015
    Location
    Missouri
    MS-Off Ver
    2013
    Posts
    2

    Formula to count number of visible rows, and formula to count visible blanks

    I have a large number of rows manually hidden on my excel sheet, and this is interfering with my count functions. Does anyone know a formula to count (or number) the number of visible rows? And is there a formula that can be used to count the number of visible blanks?

    When I use the COUNTBLANKS function, it returns to me the number of hidden and visible blanks. The subtotal(3,range) or subtotal(103,range) functions only count visible text.

    For example, in the file attached with hidden rows I'd like it to say 10 for number of rows and 6 for number of blanks.

    Thanks
    Attached Files Attached Files
    Last edited by radoncadonc; 05-28-2015 at 12:26 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formula to count number of visible rows, and formula to count visible blanks

    Using formulas, I think the only way you can do what you propose is to add at least one helper column.

  3. #3
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Formula to count number of visible rows, and formula to count visible blanks

    Is it ok to put a placeholder such as ' into the blank cells so subtotal(103,range) will work?


    If this post was helpful or at least brought a smile to your face , please click the Add Reputation button as a way to say thank you!!!!!!

  4. #4
    Registered User
    Join Date
    05-28-2015
    Location
    Missouri
    MS-Off Ver
    2013
    Posts
    2

    Re: Formula to count number of visible rows, and formula to count visible blanks

    Thanks. In the meantime since the post I did unhide a column that had data in every row, thus allowing me to determine the number of visible rows. Will this work as a helper column for the other functions?

    A placeholder would be ok

  5. #5
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formula to count number of visible rows, and formula to count visible blanks

    Earlier reply changed to "Yes"...

    For blanks just subtract subtotal(103,range).
    Last edited by jhren; 05-28-2015 at 04:35 PM.

+ 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 Cells With Visible Text
    By avendi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-25-2014, 12:47 AM
  2. Table Object after filtering - Count visible rows & First visible row
    By limalf in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-13-2013, 07:29 PM
  3. Count number of visible rows
    By gan_xl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-29-2013, 03:10 PM
  4. How to only count the number of visible rows that meet 2 conditions
    By chaz123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-04-2013, 10:15 AM
  5. Formula to Sum & Count the Visible numbers
    By realniceguy5000 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-22-2010, 09:57 AM

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