1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

(Excel) Formula for counting cells in a column

Discussion in 'Business Applications' started by mraaron012, Jul 31, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. mraaron012

    mraaron012 Thread Starter

    Joined:
    Apr 7, 2004
    Messages:
    140
    I am trying to figure out a formula for Microsoft Excel. I want to be able to count up the number of cells in a column that are being used (or have text in them).

    Is there a formula that would count this? Many thanks in advance.
     
  2. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Try using something like this...

    Code:
    =COUNTA(A1:A6)
    
    This code will only count the cells in the column that have text.
     
  3. chiliwormeater45

    chiliwormeater45

    Joined:
    Jul 2, 2007
    Messages:
    454
    Actually:

    Code:
    =COUNTA(A1:A6)
    
    will count all the cells that aren't empty. COUNTA counts values and labels. If you just want to count values, use COUNT. :D There isn't a way that I know of to just count labels.
     
  4. computerman29642

    computerman29642

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I stand corrected. Thanks chiliwormeater45 for the clarification. :D (y)
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Actually,
    Code:
    =COUNTA(A1:A6)-COUNT(A1:A6)
    would give you a count of the cells with just text in them. Of course, if you have numbers formatted as text, it will count them as text and not as numbers, so that would give an incorrect value (as also would COUNT or COUNTA).
     
  6. chiliwormeater45

    chiliwormeater45

    Joined:
    Jul 2, 2007
    Messages:
    454
    Oh right, I didn't consider numeric labels. I didn't know that you could subtract two formulas, I don't know a whole lot about spreadsheets.

    If you really want to make absolute certain that you only get labels and not numeric labels or values, you could use a macro that checks if a cell is a numeric label and only counts the cells that aren't numeric labels or values. :D
     
  7. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Think of CountA is count alpha numeric, where alpha means alphabet or letters and numeric referring to numbers. So it will count both numbers and text. If you are only interested in counting numbers then you will use a straight COUNT function.
     
  8. chiliwormeater45

    chiliwormeater45

    Joined:
    Jul 2, 2007
    Messages:
    454
    Ok sure, yeah I get that. COUNTA counts all numbers and letters, but say you don't want to count numeric labels, but you want to count all values and labels. How would you do that?
     
  9. Aj_old

    Aj_old

    Joined:
    Sep 24, 2007
    Messages:
    869
    Then you should store properly your data! I think!
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/735646

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice