Results 1 to 4 of 4

Countif only if not null... but not counta

This is a discussion on Countif only if not null... but not counta within the Excel Questions forums, part of the Question Forums category; I would like to count the cells that are not blank , but i need to ignore a formula in ...

  1. #1
    New Member
    Join Date
    Dec 2009
    Posts
    12

    Default Countif only if not null... but not counta

    I would like to count the cells that are not blank , but i need to ignore a formula in the cell.

    countA doesn't ignore the formula.

    can i do it with countif?

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    31,857

    Default Re: Countif only if not null... but not counta

    perhaps

    =SUMPRODUCT(--(A1:A10 < > ""))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    63,507

    Default Re: Countif only if not null... but not counta

    Quote Originally Posted by randomguy View Post
    I would like to count the cells that are not blank , but i need to ignore a formula in the cell.

    countA doesn't ignore the formula.

    can i do it with countif?
    If text values are what you want to count excluding formula blanks...

    =COUNTIF(A2:A100,"?*")

    If numbers also must be included...

    =COUNT(A2:A100)+COUNTIF(A2:A100,"?*")
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Dec 2009
    Posts
    12

    Default Re: Countif only if not null... but not counta

    That worked, thank you. I just poined it to the zipcode field and used the number version =)

    Quote Originally Posted by Aladin Akyurek View Post
    If text values are what you want to count excluding formula blanks...

    =COUNTIF(A2:A100,"?*")

    If numbers also must be included...

    =COUNT(A2:A100)+COUNTIF(A2:A100,"?*")

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com