COUNTIF default criteria

[Scott Huish]

I never tried this until today, but if you exclude the criteria for COUNTIF, and presumably the other ..IF functions, it is assumed to be 0. You still need to include the , but the criteria is not required. Just thought this was interesting.

=COUNTIF(A:A,)

will count all zeros in column A.

 

[Fazza]

Yes, it is (interesting), Scott.

Excel help says the criteria is required! So that isn't right..

regards, Fazza

 

[Aladin Akyurek]

We see sometimes:

=VLOOKUP(D2,$A$2:$B$4,2,)

=MATCH(D2,$A$2:$A$4,)

This is also done, too often, in OFFSET specs.

The MS rule is: Nothing after the comma, assume 0. This covers COUNTIF to as you observed.

 

[XOR LX]

Interesting, Scott!

You inspired me to look for other examples. This returns the relative position of the first zero in the range A1:A10 (and is aesthetically-pleasing!):

=MATCH(,A1:A10,)

Regards

 

[Colin Legg]

We had a similar discussion on the IF() function here:
http://www.mrexcel.com/forum/excel-questions/387469-if-function-syntax-option.html

 

[XOR LX]

We had a similar discussion on the IF() function here:
http://www.mrexcel.com/forum/excel-questions/387469-if-function-syntax-option.html

Thanks. A very interesting discussion as well.

Regards

 

[Jon von der Heyden]

I really don't know if there is any benefit omitting function arguments. Sometimes I inherit a spreadsheet and see:

=VLOOKUP(A1,Sheet1!A1:B100,2)

or...

=VLOOKUP(A1,Sheet1!A1:B100,2,)

And then I'm worried that the person who wrote the formula may have inadvertently left off the closing argument (because each of those formulae work completely differently)...

And we [or rather DonkeyOte] (fairly) recently discovered that this is volatile:

=SUMIF(A1:A100,"criteria",B1)

And I inherited a project 6 or so months ago and this is exactly the construct that the previous developed used. I reckon the person that wrote it saw the discussion on one of the forums and thought "wow that looks cool" and didn't bother to understand the implications of employing volatile formulae in his workbook.

I also very explicitly include COUNTIF(S)/SUMIF(S) comparison operators. I once had a range of values to sum up using a criterion and the criterion was in the format of <[A-Z]####>. So I had e.g. =COUNTIF(A1:A100,B1) where B1="<B1000>". This returned incorrect results because even nullstrings within A1:A100 were included in the result (other non-equalling values are included too). And so it became necessary to explicitly pass the comparison operator, e.g. =COUNTIF(A1:A100,"="&B1). If we fail to pass a criterion value to the final argument then we lose the ability to explicitly pass the comparison operator too.

So whilst I do find it interesting (these finds reveal much to us about how functions work) - I don't encourage omitting function arguments... It leads me to question the integrity of the results, which makes me have to study the formulae more, and in turn costs more time...

 

[RoryA]

I don't think your SUMIF/COUNTIF examples are the same thing - it's an incomplete argument, not a missing one.