Excel Formulas and Functions Tutorial

Using the SUMIF function in Excel

The SUMIF function allows users to sum a range of cells based on specific criteria. For example, in the following table there are 10,000 rows of individual sales and we want to be able to sum up the sales for each of the salespeople. This can be done using an excel table or pivot table, however the benefit to using a SUMIF function is that the data can viewed quickly and in once place, especially when used in conjunction with a list

The SUMIF function is has the three following parts

1. Range
2. Criteria
3. Sum Range

The range is generally going to be a table or database where the information is stored. In the example above is the table A5:K10001.

The criteria is the very similar to a look up value in a VLOOKUP function. In this case the criteria will be the salespersons name. In other words, we are asking the function to find the criteria within the range, or find the salespersons name within our table. As is the case with a look up function the criteria has to be in the first column of the range.

The sum range is the data we want to sum up, in this case that is column I, the sales or range I6:110001. In other words, we are asking the function to return the total sales for the salesperson within the highlighted range.

This is what the function will look like once completed, presuming we have the salespersons name is cell E3.

Now we can simply update the sales person’s name and our SUMIF function will automatically update the sales to represent that person.

Excel Nested Functions

In Excel you can nest function inside of other functions. For example, the IF function on its own is limited because it can only use once criteria. However, when you next an AND or an OR function within an IF function you can use multiple criteria. In the example below I want the answer to be TRUE or FALSE if both China and Japan are in cells D22 and E22 respectively. An IF function on its own could not acive this so I am going to nest an AND function in the formula.

The function will look like this

As you can see I have simply used an AND function as my logical argument within the IF function. This has allowed me to use more than one criteria for my argument.

There are many instances when you will find it useful to be able to nest a function within another function. In some cases functions have been designed only for nesting purposes, like the IFEFFROR function.

See all Seven Institute Excel training Options