+ Reply to Thread
Results 1 to 9 of 9

Counting Data Which Doesn't Exist..

  1. 07-31-2013, 01:20 PM #1
    tommyfernandez is offline
    Registered User
    Join Date
    08-24-2007
    MS-Off Ver
    14.0
    Posts
    69

    Counting Data Which Doesn't Exist..

    Hello,

    I cannot figure out why the Count function counts blank cells.. Data adjacent to the blank cells were pasted from Access datasheet. Any thoughts?

    -Tommy
    Reply With QuoteRegister To Reply
  2. 07-31-2013, 01:29 PM #2
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: Counting Data Which Doesn't Exist..

    have you tried COUNTA? I believe COUNTA counts non blank cells only.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  3. 07-31-2013, 01:39 PM #3
    LaffyAffy13 is offline
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Denver, CO
    MS-Off Ver
    365
    Posts
    230

    Re: Counting Data Which Doesn't Exist..

    If you are using the COUNT function to assume a range of cells to be copied and pasted, then COUNTA may not be enough. COUNTA will count the number of cells that are not empty. Let's say its 17 used cells in a range of 20. So 3 cells are empty. If you use COUNTA to copy and paste your data, then you will only copy 17 adjacent cells, and that may include the blank ones. I recommend either adding a text filter to only show rows with data entered into your specified range, or using VBA to move the data by only selecting the cells that have contents. Does that make sense?
    ~~LaffyAffy13~~

    If I have helped you solve your problem, please be sure to click at the bottom left of my post.

    Thanks.
    Reply With QuoteRegister To Reply
  4. 07-31-2013, 01:40 PM #4
    tommyfernandez is offline
    Registered User
    Join Date
    08-24-2007
    MS-Off Ver
    14.0
    Posts
    69

    Re: Counting Data Which Doesn't Exist..

    Yes, and CountA still counts blank cells. Formatting looks normal.. Think there is something I am just not seeing...
    Reply With QuoteRegister To Reply
  5. 07-31-2013, 01:49 PM #5
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.96 for Mac MS 365
    Posts
    8,690

    Re: Counting Data Which Doesn't Exist..

    Yes, sounds like you have something hidden still in the cells because I just tried it again and it worked for me, didn't count blanks and did count non-blanks.
    Reply With QuoteRegister To Reply
  6. 07-31-2013, 01:54 PM #6
    LaffyAffy13 is offline
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Denver, CO
    MS-Off Ver
    365
    Posts
    230

    Re: Counting Data Which Doesn't Exist..

    COUNTA does not count blank cells. But, if you used to have data in the cells before, they may still be considered "used". Try clearing the contents of those blanks cells that you are somehow counting, and then immediately save your file right after. Then, try again.
    Reply With QuoteRegister To Reply
  7. 07-31-2013, 01:55 PM #7
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: Counting Data Which Doesn't Exist..

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  8. 07-31-2013, 02:21 PM #8
    tommyfernandez is offline
    Registered User
    Join Date
    08-24-2007
    MS-Off Ver
    14.0
    Posts
    69

    Re: Counting Data Which Doesn't Exist..

    I think it is figured out.. I highlighted the affected blank cells and hit "Delete", and now the blank cells do not get counted. Something, which I cannot see, must have transferred over with the rest of the data during the cut/paste process.

    Thanks, all, for the input!
    -Tommy
    Reply With QuoteRegister To Reply
  9. 07-31-2013, 02:28 PM #9
    LaffyAffy13 is offline
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Denver, CO
    MS-Off Ver
    365
    Posts
    230

    Re: Counting Data Which Doesn't Exist..

    If it happens again, try doing what you just did and then saving the file right afterwards.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lsit From Data Validation References Sheet That Doesn't Exist!
    By clattenburg cake in forum Excel General
    Replies: 2
    Last Post: 07-25-2013, 11:49 AM
  2. Don't know how to display data it in pivot table that doesn't exist in the org source but
    By Serdica in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-10-2013, 04:19 PM
  3. Cell References data that doesn't exist?
    By hokeyplyr48 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-31-2008, 05:41 PM
  4. [SOLVED] Copy Worksheet to a new Workbook creating if it doesn't exist and add more Worksheets if it does exist
    By pete.bastin@btopenworld.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-18-2006, 01:10 PM
  5. Am I Looking For Something That Doesn't Exist?
    By seanryann in forum Excel General
    Replies: 12
    Last Post: 04-01-2006, 01:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1