+ 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
    4

    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
    O365 v 2402
    Posts
    13,443

    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... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    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
    44,086

    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 - Retired Excel/VBA Consultant

    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
    44,086

    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
    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,349

    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" 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.

  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
    44,086

    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
    4

    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