“The key to remember when using these operators: They can be applied to the actual sum range OR any of the criteria ranges.”
A practical example: Using comparison operators with ‘SUMIFS’
In this example we are looking at order quantities for our t-shirt business.
Our sum range will be the ‘Qty’ column but this will also be our criteria range since we will be applying comparison operators based on a chosen quantity filter.
One particularly important note about the syntax when using comparison operators: It is required to place the operator and the value within double quotes.
You will notice this in the following formula illustrations.
The first order of business is to find the sum of only quantities ordered that are greater than 35.
Since our sum range is G4:G21, our formula should like this:
Our second task is to find the sum of all those orders of 35 or less in quantity.
Our formula in this case would be:
Lastly, we would like to know the total quantity for all orders except those of 35 in quantity.
In this case, our formula will look like this:
In each of these cases, everything remains the same except the comparison operators.
Let’s review before we take a look at the results:
In the first case, our goal is to find the sum of all order quantities greater than 35. Since this includes only those quantities greater than (but not equal to) 35 we used the ‘>’ operator.
Next, we need to know the total quantity for all orders of quantity 35 or less. Since this now includes quantities of 35 as well as those less than 35, our operator should be ‘<=’.
And last, we need to find the total quantity of all orders except for those of quantity 35 and therefore our operator should be ‘<>’ which literally means less than or greater than. Put differently, we can also say this is ‘not equal’ to 35.
Now that we have covered the technical piece of constructing the formulas to find our desired results, let’s take a look at our data and actual results!