+ Reply to Thread
Results 1 to 6 of 6

How Do I Count Nonblank Cells if Blank Cells Have Formulas?

  1. #1
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    2021
    Posts
    205

    How Do I Count Nonblank Cells if Blank Cells Have Formulas?

    I want to count the number of cells in a column that have visible values. I tried the COUNTA function, but that function is counting all my cells, even the blank ones. This is because the blank cells contain formula that make them blank. I can paste as text, but I need the formulas to run while the Count function operates. How can I accomplish this?

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: How Do I Count Nonblank Cells if Blank Cells Have Formulas?

    how about counta-countblank?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    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,093

    Re: How Do I Count Nonblank Cells if Blank Cells Have Formulas?

    Are the values produced by the formulae numbers, or text, or a mix?
    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


  4. #4
    Forum Expert dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    365(PC) V:2202
    Posts
    1,468

    Re: How Do I Count Nonblank Cells if Blank Cells Have Formulas?

    How about...
    =countif(range,"")

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How Do I Count Nonblank Cells if Blank Cells Have Formulas?

    This formula will count values but ignore "null strings" returned by formulas

    =SUMPRODUCT(0+(LEN(A1:A100)>0))
    Audere est facere

  6. #6
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Chattanooga, TN
    MS-Off Ver
    2021
    Posts
    205

    Re: How Do I Count Nonblank Cells if Blank Cells Have Formulas?

    thanks guys! I just needed to use COUNT instead of COUNTA

+ 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. Count blank cells in a range and contiguous blank cells also as single cells! Tricky One!
    By SebastianColombia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2015, 02:32 PM
  2. [SOLVED] Count only filled, nonblank cells
    By jomili in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2014, 10:47 AM
  3. Replies: 2
    Last Post: 04-29-2013, 04:20 AM
  4. Replies: 2
    Last Post: 12-22-2011, 07:05 PM
  5. Count colors and nonblank cells
    By LostInAus in forum Excel General
    Replies: 1
    Last Post: 12-03-2006, 09:21 PM
  6. [SOLVED] Count NonBlank Cells
    By Lynn in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-21-2006, 06:25 PM
  7. Count Nonblank Text Cells
    By [email protected] in forum Excel General
    Replies: 13
    Last Post: 03-02-2006, 02:25 PM

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