Subtotal in Excel

While working in the Excel program, the user may face the need to sum up, in addition to the usual general.

The article will consider the table for the sale of goods for the month, since this function is most often used for the presented operation. It will look like three columns: product name, date and amount of revenue. Using in "Excel" interim results, it is possible to calculate the daily earnings of a product. In addition, at the end you can sum up the sale of goods. Now we will take a closer look at how the intermediate results in Excel work.

Terms of use

It is necessary to specify at once that for use of the presented operation the table should correspond to requirements. In total it is necessary to observe three points:

• The header in the table should be located on the first line of the sheet;
• The cell area must be formatted by default;
• The table must be filled with data.

If all these requirements are met by you, then you can easily sum up the subtotal. Now let's take a closer look at the process itself.

Create a subtotal. Using a special tool

How to create a subtotal in Excel? The first method will be demonstrated when using the standard tool of the same name in the program. So, let's get started.

Step 1: Open the tool

First, you need to open the "Subtotal" tool itself. It is located on the toolbar in the "Data" tab (in the "Structure" tool group). Before opening it, you will need to select the cell in which one of the product names is located, in this example select "Potato".

Step 2: Set up the display

After clicking on the tool, a window will open, in which you must specify the parameters for displaying information in the program. In this case, you should know the amount of revenue for a certain day, so in the drop-down list "With every change" you need to select "Date".

Since we will calculate the amount, then in the "Operation" drop-down list, you must select the "Amount" value. It is also worth noting that at this stage you can select other variables, depending on your needs. Among the proposed there are:

• minimum;
• quantity;
• maximum;
• composition.

It remains to determine only where the result will be output. To do this, in the field "Add totals by" you need to check the box next to the name of the column in which the result will be displayed. Since we calculate the amount, we choose "Amount of revenue, rub.".

Also in this window there are several more variables: "Replace current totals", "End of page between groups" and "Results under data". Of the important it is possible to note only the first item, and the rest install as desired.

Step 3: Final

After making all the settings, you just need to click on the "OK" button. Now in the program you can watch the subtotals by dates. It is worth noting that with the help of the minus sign in the left part of the window you can collapse and expand the groups. At the bottom, the grand total will be summed up.

Create a subtotal. Using a special formula

In addition to the above, how can you calculate the intermediate results in Excel? The formula is what will help us.

Those who often work with the program know that it is easier to use the special "Master of functions" tool to enter formulas. It is to him that we turn.

Step 1: Open the Function Wizard

Before you click on the desired totals, you must select the cell in which they will be displayed.

Just select any cell, without the data entered in it. Next, click on the "Insert function" icon, which is located next to the input line. You can see the exact location on the picture below.

The window of the function wizard opens, in which you will select "INTERMEDIATE.TOGS" in the "Select function" field and click "OK". To facilitate the search, you can use the alphabet sorting by selecting the "Full alphabetical list" in the "Category" drop-down list.

Step 2: Enter function arguments

As a result of all the above actions, a window opens in which you need to specify all the necessary arguments. Consider everything in order. In the line "Function number" it is necessary to indicate the number from the list, where:

In our case, you need to calculate the sum, so you need to enter the number "9".

In the field for entering "Reference 1", you must select the area of the cells with the data that will be processed using the function. To do this, you can use two ways: enter everything yourself or specify using the cursor. We will use the second option, since it is simpler. To do this, click on the button on the right and select the desired area, and then click on this button again.

Again, we will see a window familiar to us, in which you can specify the second link (there are only four of them). If you do not need to do this, then click on the "OK" button and the results will be displayed in the table.