+ Reply to Thread
Results 1 to 4 of 4

how to ignore the numeric cells when giving formula to count cells that contains text only

  1. #1
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    how to ignore the numeric cells when giving formula to count cells that contains text only

    Hi,
    =counta(a:a) or counta(1:1) is the formula for calculating the cells that contain any entries only in row or column in excel.
    This formula takes any entry, be it date or time or text or number when counting the cells in row or column.
    I want to omit the numbers, cells containing numeric cells from this formula. How to modify the formula.
    If suppose, i have entered 4 columns say aam amb eir eic then the formula =counta(1:1)give the answer 4 It omits the blanks . But if i add a column and include 15, or time or date, it shows 5. I want to omit the contents of numbers . How to modify. i have not included any work book.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: how to ignore the numeric cells when giving formula to count cells that contains text

    try
    =COUNTIF(1:1,"?*")
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    07-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    141

    Re: how to ignore the numeric cells when giving formula to count cells that contains text

    Quote Originally Posted by martindwilson View Post
    try
    =COUNTIF(1:1,"?*")
    Thank you, Very much.
    For the sake of knowing. How that "?*" omits the number and all other things except text.
    Additional sub question, how to ignore the text , if in the same condtion.
    Thank you so much for jet speed reply

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: how to ignore the numeric cells when giving formula to count cells that contains text

    If your blank cells are truly blank/empty, and do not contain a formula answer of "", there here is another possibility...

    =counta(1:1)-count(1:1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  2. Count number of cells in column per month, ignore blank cells
    By lamdl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-13-2014, 08:50 PM
  3. [SOLVED] Formula needed to only count total cells entered and not count adjacent text entered cells
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2013, 06:22 PM
  4. [SOLVED] count cells that contain text but do not count cells containing only a formula
    By cprpacific in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-13-2013, 03:15 PM
  5. Replies: 12
    Last Post: 09-17-2012, 01:03 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