Using greater than and less than in conditional formulas

By Neil Shorney, Director, Naturally Sales Ltd.

Here is a problem using SUMIF and COUNTIF with a slightly counter-intuitive example, yet a problem which can occur quite frequently. We've previously looked at how we can count IF a variable is equal to a particular value. It gets a little more tricky if you want to count (or sum) when a variable is greater than a particular amount.

The problem is that > is considered a "special character" and can't be used as-is in formulas. We get around this problem by enclosing it in speech marks as follows:

       =COUNTIF(A:A,">8")

It may look a little odd, but it works and is a very useful formula.

Coming up: Microsoft Excel level 3 live e-learning

Don't miss our next online Excel course running Friday next week, 29th April, at 2-4pm UK or 9-11am Eastern Time. You'll learn about such essential data tools as Pivot Tables, Data Validation and Macros, all in 2 hours from the comfort of your desk! Full details here or book just here.