Apparent defect with COUNTIF and array formula

[TomCon]

Do you confirm this behavior...or see an error...or have a workaround (other than make a new col on the worksheet, the obvious workaround)

You can use an array formula with ABS for many numeric functions (all these are fine/correct, give right answer):
=AVERAGE(ABS(Adj!$AD$3219:$AD$5688))
=MAX(ABS(Adj!$AD$3219:$AD$5688))
=COUNT(ABS(Adj!$AD$3219:$AD$5688))

But apparently not with COUNTIF. Excel will not let me press return with the formula without kicking up the "are you trying to type a formula, start with an =" message.
=COUNTIF(ABS(Adj!$AD$3219:$AD$5688),">2")

Thoughts?

Thanks!

 

[steve the fish]

You cant do that with COUNTIF. It expects a range as the first argument. You could sum two COUNTIFS together. >2 and <2.

 

[steve the fish]

You could also use a SUMPRODUCT that acts like a COUNTIF.

=SUMPRODUCT(--(ABS(Adj!$AD$3219:$AD$5688)>2))

 

[Marcelo Branco]

COUNTIF accepts only ranges as the first parameter. It doesn't accept arrays.

As a workaround try...
=SUMPRODUCT(--(ABS(Adj!$AD$3219:$AD$5688)>2))

M.

 

[jasonb75]

It is not a defect with the function, excel is rejecting an attempt to use the function incorrectly. As pointed out in previous replies, the function is expecting a range. This means that it must be a valid reference to a range on a worksheet, or a function such as INDIRECT that returns a valid reference. This applies to many other functions, not just countif. If you look at the tooltip that is shown while you are typing the formula, it tells you what is required. Anything that says Range, Reference, or an abbreviation of Reference, needs a valid range reference as previously stated, it will not accept anything else.

 

[Peter_SSs]

Yet another alternative would be

=COUNT(FILTER(Adj!$AD$3219:$AD$5688,ABS(Adj!$AD$3219:$AD$5688)>2,""))

 

[TomCon]

Thanks for all the suggestions. Many approaches to doing it without cluttering the data with an unneeded column. I like to consolidate things like this into formulas and not adding unnecessary columns to the data.

Also thank you for clarification about input to COUNTIF. To me all the inputs seemed "transparent" and i did not realize there was a distinction between the REQUIREMENT for specifically a Range, and something that returned what looked to me "just like a range" I simply thought they were the same (as they usually are in most formulas), but i now see that they may not necessarily be the same for all functions. I think the fact that Microsoft made that distinction is a bit confusing, but i see here that you have to "learn the nuances" as well.

thank you much for the thorough comments! Unfortunately it only lets me mark one as a solution...but they all work!