Using comparison operators and blank/non-blank cells
Let’s consider a scenario in which we have orders still outstanding and we need to draw conclusions from our data based on that fact.
In the table we have already been using as our example, there are some rows that contain a ‘Received’ date but others that do not.
The latter indicates those orders that were placed but have not yet been received.
For this example, we want to use the ‘SUMIFS’ function to find three things:
1: The total quantity of products ordered in aggregate that have not yet been received
2: The total quantity of products ordered during the current month not yet received
3: The total quantity of products ordered during the previous month not yet received
This is the point which we should talk about comparison operators as they relate to blank and non-blank cells.
“While in the previous example we used comparison operators to establish some criteria based on an actual value or cell reference containing a value, we can also use comparison operators in a different way to set some criteria based on whether a cell reference contains data or not.”
- If we want to sum values based on corresponding cells that are blank, we use the criteria ‘=’.
- If we want to sum values based on corresponding cells that are non-blank, we simply use ‘<>’.
For the first objective in our next example, our only criteria will be related to the ‘Received’ dates.
More specifically, we simply need to find the sum of quantities ordered for all rows in which their ‘Received’ field is blank so we will put ‘=’ to use.
Our sum column is still the ‘Qty’ column or column G, but our criteria column is now the ‘Received’ column, or column F.
Therefore, since our first objective is to find the sum of all orders yet to be received, our formula should be:
This formula says to sum all values in the range G4:G21 (our ‘Qty’ column) that correspond to blank cells (‘=’) in the range F4:F21 (the ‘Received’) column.
In the next objective of our example, we will impose a filter within the SUMIFS formula to select only the rows with a date in the ‘Ordered’ column of May or later.
“We will do this simply by using a comparison operator on an actual value (date) like we did in our previous example set.”
Then we will impose the same criteria for summing only values in our ‘Qty’ column that correspond to cells in the ‘Received’ column that are blank.
Recalling what we have already learned up to this point, we should easily be able to implement everything to create the following formula:
This formula is almost exactly like the previous example except we now have the criteria range and criteria parameter related to cells in the ‘Ordered’ column with a date value greater than the value in cell ‘J4’ which is ‘1-May’.
Instead of hard coding the actual date into the criteria argument for this parameter, we have used a cell reference that contains the value we want specified.
“Note the syntax for referring to a cell reference when using a comparison operator. The method consists of placing the ‘&’ character just between the ‘>’ and the cell reference.”
So what our formula is saying is to sum the values in the ‘Qty’ column that correspond to both cells in the ‘Ordered’ column that are ‘1-May’ or later and cells in the ‘Received’ column that are blank.
Clearly, this should give us a different result than our first objective result where we were seeking a sum of quantity values for all of our data.
Our third and last objective for this example is to find the quantity of items ordered the previous month that have not yet been received.
Put another way:
We will be filtering the values we want to sum from our ‘Qty’ column corresponding to blank cells in the ‘Received’ column based on values in the ‘Ordered’ column that are both greater than the first day of the previous month and less than the first day of the current month.
… That was a sentence-full so let’s take a look at what the actual formula looks like for this requirement:
Compared to the previous objective for this example, we have added criteria that imposes a filter to our data based on the date range in the ‘Ordered’ column being greater than the value in cell ‘J5’ (‘1-Apr’) but less than the value in ‘J4’ (‘1-May’).
All other elements of the formula have remained the same.
Let’s take a look at the results of our work now that we have stepped through the technical piece of constructing the ‘SUMIFS’ formulas for our three objectives.
Note that if you evaluate the values in the ‘Qty’ column that correspond to blank cells in the ‘Received’ column AND a May date in the ‘Ordered’ column, the sum of those values should be 115 (40 + 40 + 35 = 115).
It is also true if you isolate quantity values corresponding to those cells with a blank in the ‘Received’ column and an April date in the ‘Ordered’ column you should get a sum of 90 (40 + 50 = 90).
It follows that for all orders yet to be received, the quantity total should be 205, or the sum of both of the previous results.