Consulting

Results 1 to 3 of 3

Thread: Utility of countif

  1. #1
    VBAX Contributor
    Joined
    Jun 2006
    Posts
    135
    Location

    Utility of countif

    I was wondering if countif has still a utility. It appears to me that the sumproduct enables to do all that contif does, including the criteria as an array. For instance the criteria <>"" can be replaced by an array --(A1:A10<>"") in a sumproduct.

    Is this function kept to keep using formulas written in formers version or has it still a true utility (a case when sumproduct might not work), or is it simply much faster?

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,938
    Location
    It's a freak byproduct really. I don't think it [SUMPRODUCT] was intended to be used that way by the Office developer team. Check out this link on the formula, and also this link.

    HTH

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Quote Originally Posted by jungix
    I was wondering if countif has still a utility. It appears to me that the sumproduct enables to do all that contif does, including the criteria as an array. For instance the criteria <>"" can be replaced by an array --(A1:A10<>"") in a sumproduct.

    Is this function kept to keep using formulas written in formers version or has it still a true utility (a case when sumproduct might not work), or is it simply much faster?
    It does, but SUMPRODUCT is substantially slower than COUNTIF, so where possible it is better to use COUNTIF and SUMIF, especially if you have a large, formual intensive spreadsheet.

    You can even simulate a double test in COUNTIF (such as >5 and <10) like so

    =COUNTIF(A1:A100>5)-COUNTIF(A1:A100)>10

Posting Permissions

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