There are eight functions in Excel that work differently than you might realize.
What’s different?
Once you understand how these functions work, you could more easily get them to do what you want.
In addition, you’ll have a better idea about when you should explore alternatives.
With theIF functionthe syntax is simple:
Nothing special, right?
This requires the following formula:
Notice anything strange about this formula?
What’s that text (">5") doing in there?
Isn’t 5 anumericvalue?
This happens because the logical expression A1:A10>5 has beensplitinto two parts.
As a result, it must appear in quotes like “>5”.
For example, let’s say we want to check values greater than B1.
Now we need to write:
We are countingnumericvalues, but do we need to use bothconcatenationandquoted text?
But note the cell reference itself isnotquoted :)
What about text values?
Suppose we want to test whether a cell equals “apple”.
So, the logic is simpler, but quirky.
Ranges are required
A second key difference with RACON functions is range arguments.
This may not seem like a big deal at first.
After all, the data is on the worksheet, right?
So why not supply a range?
However, in real life situations, this requirement has consequences.
One example is dates.
Let’s say you want to check dates in A1:A10 to see which ones are in June?
Now, how can wecountthe dates in A1:A10 that are in June?
You might think we could use the MONTH function like we did with IF:
Nope.
While this looks perfectly reasonable, it isn’t going to work.
Excel won’t even let youenterthe formula.
Instead, it will throw a generic “There’s a problem with this formula” error.
In fact, the “problem” is that you must supply arangeas the first argument to COUNTIFS.
Okay, so how can you get COUNTIFS to count dates in June?
- To be clear,spill rangeswork fine in RACON functions, since they are ranges on the worksheet.
