+ Reply to Thread
Results 1 to 9 of 9

Counting cells, including blank cells, without manually adjusting a range

  1. #1
    Registered User
    Join Date
    07-09-2020
    Location
    Cambridge, England
    MS-Off Ver
    2016
    Posts
    3

    Counting cells, including blank cells, without manually adjusting a range

    Good morning,

    A have a worksheet with many columns of data. In each column, some of the cells contain entries, but some are blank. I'm looking to count the number of cells which contain entries including the blank cells along the way, but not beyond the last cell with an entry in it. Currently I'm using the formula below:

    =(COUNTA(M7:M))+(COUNTBLANK(M7:M118))

    The inconvenience being that the data is on a Google Sheet, and new entries regularly come in as the data is on an IMPORTRANGE function. So, I regularly have to log in and adjust the COUNTBLANK range for each column to ensure any blanks are counted, but I can't just put the COUNTBLANK all the way to the bottom because if would count the empty cells forever.

    My query is, is it possible to write a formula that would count all the cells (blanks included) right down to the last cell with an entry in it, preferably one I don't have to manually adjust so often?

    Many thanks for your help, and please let me know if I can provide any more information.

    Dave

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,515

    Re: Counting cells, including blank cells, without manually adjusting a range

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

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Counting cells, including blank cells, without manually adjusting a range

    SUMPRODUCT with whole column ranges... messy and slow. It will calculate 1,000,000 rows!!

    Can you post a sample, showing exactly what you mean.... Are the values TEXT or NUMBERS, or a mixture? Are the balnk cells empty or do they contain formula blanks?

    see yellow banner (top of page) for instructions on attaching files.
    Glenn



  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,515

    Re: Counting cells, including blank cells, without manually adjusting a range

    Quote Originally Posted by Glenn Kennedy View Post
    SUMPRODUCT with whole column ranges... messy and slow. It will calculate 1,000,000 rows!!
    Give it a try first Glenn ( as I did), result is immediate ( tested when entering a value in row 1000000). Thanks for the compliment, anyway...
    Last edited by Pepe Le Mokko; 01-08-2022 at 08:02 AM.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,011

    Re: Counting cells, including blank cells, without manually adjusting a range

    If the data in the column is alphanumeric then =MATCH("zzz",$M:$M,1)-6

    If it is numeric then =MATCH(9.999E+307,$M:$M,1)-6

    If it is a mix then =MAX(MATCH(9.999E+307,$M:$M,1),MATCH("zzz",$M:$M,1))-6
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,011

    Re: Counting cells, including blank cells, without manually adjusting a range

    -ROW(M7)+1 = -6 Ö maybe, over-engineered?
    Last edited by TMS; 01-08-2022 at 12:23 PM.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,463

    Re: Counting cells, including blank cells, without manually adjusting a range

    Eh??? 8, surely?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,011

    Re: Counting cells, including blank cells, without manually adjusting a range

    Meant to be -6. Note the minus sign in front of the ROW(M7). My mistake.

  9. #9
    Registered User
    Join Date
    07-09-2020
    Location
    Cambridge, England
    MS-Off Ver
    2016
    Posts
    3

    Re: Counting cells, including blank cells, without manually adjusting a range

    Many thanks for your help Pepe, and every one else who replied to this post. Pepe, your formula worked perfectly first time.

+ 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. Replies: 12
    Last Post: 11-21-2018, 11:09 AM
  2. Specify range of cells including spaces, manually typed text and line breaks in VBA
    By LindaABH in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2012, 12:59 AM
  3. Replies: 3
    Last Post: 04-09-2012, 02:53 PM
  4. Replies: 9
    Last Post: 10-12-2010, 12:37 PM
  5. Count blank cells within a range not including fully blank rows
    By twofootgiant in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 07-16-2008, 09:43 AM
  6. Average range including blank cells: #DIV/0!
    By Areaka in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2006, 05:21 PM
  7. Replies: 0
    Last Post: 08-23-2005, 03:43 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