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 implementation tableof the goods for a month, as 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 are made in Excel.


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 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 tool itself"Subtotal". It is located in 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".

excel subtotals

Step 2: Customize the display

After clicking on the tool, awhich you need to set the parameters of information output in the program. In this case, you should find out 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 inIn 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 only to determine whereoutput the result. To do this, in the field "Add a total 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, rubles."

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 at will.

Step 3: Final

After making all the settings, you only haveclick on the "OK" button. Now in the program you can watch the subtotals on 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 pile up the desired totals, you need to select the cell in which they will be displayed.

Simply select any cell, without entering itdata. 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.

subtotals in Excel

The Function Wizard opens in which the"Select function" you need to highlight "INTERMEDIATE.TOGS" 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 of the abovea 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:

excel interim results formula

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

In the field to enter "Reference 1" you must select an areacells with data to be processed using the function. To do this, you can use two ways: enter everything yourself or specify with 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.

excel interim results formula

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

  • Rating: