0:00

[MUSIC]

In this section, we will be talking about the subtotal function in Excel.

The subtotal function returns a subtotal value from a list or a database.

You can use it to calculate a sum, an average, a count,

as well as a number of other mathematical functions that are useful on working

with data sets.

You may be wondering why anyone would use subtotal to sum a set of values

in the list,

when I could just value directly using the sum function.

There are two major reasons for using subtotal.

The first,

being that it will allow you to perform dynamic calculations on a data set.

What this means is that

if you were to filter that data set down to a select set of points,

the subtotal function will calculate the sum of only the points that you selected,

whereas sum function will still be calculating the some of the entire range

even though those fields have been hidden.

The second reason for using subtotal is that it ignores

any fields containing a subtotal function that are within the data set itself.

This means that you can sum a list containing multiple subtotal sums

without having to worry about double counting.

The subtotal function takes two main arguments.

The first, being the function that you want Excel to perform and the second,

being the data set on which you want to perform the function.

Now, let's practice subtotals.

In exercise 1, you'll notice that we have been given a data set that shows

the month, the team and the total sales that those teams made.

If we select the sales totals,

we will notice that this column is in is a named range labeled as sales A.

We will be using named ranges going forward.

First, we are asked to calculate the sum of the sales,

so let's start by typing = in cell G12, followed by sub.

We then select the named range sales A.

Pressing enter, we see that the total sum is $18,413.

Next, we're going to calculate the average number of sales.

So, let's start by typing = again, and then typing out average.

We don't want to calculate the average of the sales, so

we're going to use the named range again and we find that the average is $1,674.

Now, let's calculate the count, seeing how many sales were made.

We type = again, then count and we select the sales data.

We can see that there're 11 cells in the table that we're currently looking at.

Now that we have calculated the sum, average and

count of the sales using the basic Excel functions,

let's do the same thing, but this time we'll be using subtotal.

First, we are going to calculate the sum of the sales.

So let's start by typing = followed by subtotal.

Notice how Excel will prompt us to provide the function that we want to perform.

Here, we want to use sum.

So let's go ahead and double click on sum in the list to select this function.

Next, Excel's going to ask us for

the data set that we want to perform this function on.

This is the named range for Sales A.

Close the parenthesis and press enter.

Notice that this gives us the same sum

as using the basic sum function which is $18,413.

Let's do the same thing to calculate the average number of sales using subtotal.

We start by typing = followed by subtotal.

We see that the average function is indicated by the number 1

in the drop down.

So, instead of double clicking on the average,

we're going to go ahead and simply type 1 followed by a comma.

Now, Excel is asking us to input the cells that we want to perform that function on.

Once again we are going to put the sales data that is a named range.

Again, notice that this gives us the same value as the basic average function

which is $1,674.

This is because we are currently showing all of the fields in the data set.

We'll talk more about this later.

Last but not least,

we are going to calculate the count of sales using the subtotal function.

Once again, we start by typing = followed by subtotal.

We see that count is the second entry in the drop down, so we type 2,

and now we select the sales data.

And once more, this is the same value that we found using the simple count function.

4:55

Now, let's see what happens when we filter the data set.

Let's start with applying the filters.

To do this, first we select headers and then we click on the sort and

filter option on the home tab, selecting filter from the drop down.

As you can see, filters are now showing on the header row of the data table.

If I were to filter the table as it is,

it will hide the rows that don't have the matching data that I am looking for.

Since our calculation fields are on some of those rows,

portions of the calculations make it hidden.

Hence, I'm going to cutter sum, average, and count calculations using the Ctrl+X

shortcut and paste them below the data table using Ctrl+V.

Now that we are all set up, let's say we want to know the total sales for

the month of January.

In order to answer that question, we go to column B and

we filter the month for Jan. Did

you notice what happened to our sum, average and count values below the table?

The values that are calculated using the basic function have not changed.

They are still calculating the total value of the entire data table.

However, the values that are calculated using the subtotal function have changed.

They now calculate only on the records that are currently showing

in the data table.