PDA

View Full Version : Utility of countif



jungix
08-08-2006, 12:50 PM
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
08-08-2006, 01:03 PM
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 (http://www.mrexcel.com/wwwboard/messages/8961.html) on the formula, and also this link (http://www.xldynamic.com/source/xld.SUMPRODUCT.html).

HTH

Bob Phillips
08-08-2006, 02:18 PM
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