Utility of countif

[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?

[Zack Barresse]

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

[Bob Phillips]

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