How to Use the Excel Functions SUMIF and SUMIFS (tutorial)

How to Use the Excel Functions:
SUMIF and SUMIFS (tutorial)

In this article, we take a look at the ‘SUMIF’ and ‘SUMIFS’ functions in Excel.
SUMIF SUMIFS Visualized

Both are quite useful in allowing us to sum data based on related criteria within the same data set.

To differentiate, the SUMIF function allows for imposing some single criteria on our data while the ‘SUMIFS’ allows for imposing multiple criteria.

For example, as we will see later we can filter t-shirt order quantities we would like to sum based on color OR size using ‘SUMIF’.

“We can even filter those quantity values based on color AND size using ‘SUMIFS’!”
Kasper Langmann, Co-founder of Spreadsheeto

Another useful way to leverage the ‘SUMIFS’ function that we will cover is by using comparison operators like equals, less than, greater than, less than or equal to, and greater than or equal to some value.

We can use these comparison operators in relation to values like dates that can be very useful and practical in the real world.

Table of contents for our guide to ‘SUMIF’ and ‘SUMIFS’

All of the examples will be performed using Microsoft Excel 2016 for Windows.

Let’s get started by looking at ‘SUMIF’!

Introduction to the ‘SUMIF’ function

If you have ever used the ‘SUM’ function in Microsoft Excel, you likely know how valuable it is even if it is easy to take it for granted.

“This simple function, like them all, really show its true value when dealing with large data sets where manual calculations become inefficient and impractical.”
Kasper Langmann, Co-founder of Spreadsheeto

This is certainly true as well for scenarios where a general sum of all values doesn’t quite meet the necessary requirements without some type of further manipulation of the data in order to focus on specific subsets of the data.

For example, maybe we have a data set that consists of inventory information.

We’d like to be able to quickly pull totals on this information based on some criteria like size, color, order date, or whether or not the inventory has even been received physically.

These are all real world requirements that ‘SUMIF’ can help us with quite handily.

How to use ‘SUMIF’

Like all Excel functions ‘SUMIF’ consist of some elements you need to put into it to make it put out the results you desire. This concept is called ‘Syntax’.

Syntax of ‘SUMIF’

The syntax for the ‘SUMIF’ function is pretty straight forward:

‘=SUMIF(range, criteria, [sum_range])’

The ‘range’ parameter is actually the range of cells that will be evaluated by the ‘criteria’ parameter.

The ‘criteria’ parameter is the condition that must be met in the ‘range’ parameter. For instance, if our ‘range’ was a column that listed t-shirt color, a value like ‘red’ or ‘white’ could be our ‘criteria’. The ‘criteria’ value can be text, a number, a date, a logical expression, a cell reference, or even another function.

*One thing to note, however, is that any mathematical expression must be enclosed in double quotes as we will see when we cover using comparison operators.

The ‘sum_range’ parameter is optional as noted by the brackets. This simply means that if omitted, the ‘sum_range’ will default to the same cells you chose for the ‘range’ parameter.

‘SUMIF’ in action

Let’s take a look at a simple example to demonstrate how ‘SUMIF’ works.

Follow along by downloading our sample file right below!

Click to Download This Tutorial’s FREE Sample File

First, we will leave out the ‘sum_range’ to see a very simple example using a single column of numerical values.

Here we have a column of numbers from B4 to B14.

First we will use ‘SUMIF’ to get the sum of all values greater than 20. Then we will use ‘SUMIF’ to get the sum of all values less than 30.

To be sure, we could easily sort our single column of values in ascending order and quickly find the first value 21 or larger and highlight all other values below it to find the sum, but let’s just go with the concept here.

“To use the ‘SUMIF’ function to find the sum of all values greater than 20 we only need 2 parameters.”
Kasper Langmann, Co-founder of Spreadsheeto

Our ‘range’ parameter is simply the range of values in the single column of data while the criteria will be ‘>20’.

Comparatively, in order to find the sum of the values that are less than 30, we simply need to change our criteria to ‘<30’.

Example of SUMIF

Of course, we could just as easily use a cell reference for our ‘criteria’ parameter rather than hard coding the actual value. This requires a bit more know how.

“The comparison operator still needs to be placed within double quotes but since the value will no longer be hardcoded into the formula and instead be replaced by a cell reference, we simply append the cell reference to the ‘>’ or ‘<’ with an ampersand (&).”
Kasper Langmann, Co-founder of Spreadsheeto

By using a cell reference, we can build a table that allows us to change the value of our ‘criteria’ parameter without changing the actual formula.

We did just this in cells E4 and E5 in the example below in the ‘Selection’ column of our ‘Criteria’ table.

The sum range is the ‘Count’ column (B4:B14) and the samples for using the ‘SUMIF’ function using a hard coded value for ‘criteria’ as well as a cell reference are also shown.

Using wildcards with ‘SUMIF’

Another very useful method of drilling down into subsets of data while using ‘SUMIF’ that is well worth noting is that of using wildcards.

“For instance, we can use the asterisk ‘*’ to match any sequence of characters.
Kasper Langmann, Co-founder of Spreadsheeto

So if we had a range of first names, we could use ‘D*’ to isolate all matching rows with names starting with a ‘D’.

Another example is ‘*e*’ which finds any string containing an ‘e’, like ‘green’, or ‘leaf’.

To illustrate, our example below shows a table with ‘Color’ and the data contains different variations of greens and blues.  In the formula, the ‘criteria’ parameter uses the ‘*’ to capture all shades of green or blue in our ‘SUMIF’ formula.

The other wildcard ‘?’ can be used to match any single character.

For example, ‘M?’  will locate any two-character string that begins with ‘M’, such as ‘Mr’, ‘Ms’, ‘M1’, etc.

Comparatively, if we used ‘?r’ we would be able to locate string values like ‘Dr’, ‘Mr’, ‘Sr’, etc.

Now that we have taken a thorough look at how to make use of ‘SUMIF’ let’s move on to using multiple criteria for summing values using the ‘SUMIFS’ function.

‘SUMIFS’ vs. ‘SUMIF’

The distinctive difference between ‘SUMIF’ and ‘SUMIFS’:

“While ‘SUMIF’ allows us to impose some single criteria on our sum, ‘SUMIFS’ allows us to impose more than just one depending on our needs.”
Kasper Langmann, Co-founder of Spreadsheeto

The syntax of ‘SUMIFS’ is as follows:

‘=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)’

Notice that the primary difference compared to ‘SUMIF’ that is immediately obvious is the first, and required, argument in ‘SUMIFS’.

Unlike the ‘SUMIF’ function, ‘sum_range’ in ‘SUMIFS’ is required.

Following this first parameter is the first criteria range and criteria pair, ‘criteria_range1’ and ‘criteria1’.

Clearly, this pair of parameters is required since without at least one of these, the function itself is useless. As requirements present the need for more of these criteria range/criteria pairs, we can add as many as necessary up to the limit of 127 pairs.

The basic gist of how to make use of these parameters holds true to that of ‘SUMIF’, so we will not repeat those same basic concepts.

“We will transition into some of the ways in which to use ‘SUMIFS’ knowing what we have already learned in using ‘SUMIF’.”
Kasper Langmann, Co-founder of Spreadsheeto

Using comparison operators with ‘SUMIFS’

A very useful feature of the SUMIFS function is the ability to use comparison operators like ‘=’, ‘>’, ‘<‘.

Furthermore, these can be used together to create the comparison operators:

‘>=’ (greater than or equal to)

‘<=’ (less than or equal to)

‘<>’ (less than or greater than/not equal to)

“The key to remember when using these operators: They can be applied to the actual sum range OR any of the criteria ranges.”
Kasper Langmann, Co-founder of Spreadsheeto

A practical example: Using comparison operators with ‘SUMIFS’

t shirt example
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:

resultsIn 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!

“Using comparison operators in the criteria of our ‘SUMIFS’ formulas are clearly a very useful way to filter our results as needed.”
Kasper Langmann, Co-founder of Spreadsheeto

Another fascinating way to make use of comparison operators with the ‘SUMIFS’ function can be seen in the next example…

Using comparison operators and blank/non-blank cells

ordersLet’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.
Kasper Langmann, Co-founder of Spreadsheeto
  • 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.”
Kasper Langmann, Co-founder of Spreadsheeto

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.”
Kasper Langmann, Co-founder of Spreadsheeto

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.

reviewing resultsNote 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.

Conclusion

It is clear from what we have covered that the simplicity of ‘SUMIF’ and ‘SUMIFS’ is quite deceiving and that it is only once we dig into some real world type of scenarios that we see the real possibilities. These examples merely scratch the surface.
conclusion

With a little creativity, anyone can combine the ideas of comparison operators with wildcards or even other functions to develop some useful solutions to complex problems within data.

“This is the rewarding thing about learning functions like ‘SUMIF’ and ‘SUMIFS’: It exposes us to possibilities of how to develop elegant solutions to complex problems.”
Kasper Langmann, Co-founder of Spreadsheeto

CLICK HERE to try our free Excel training.

2017-03-20T10:53:27+00:00

Send this to friend