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,

Â where is some function will still be calculated in 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 $1674.

Â 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 is, 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 $1674.

Â 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 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 stile 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.

Â