SUBTOTAL function - The calculation function for a group in a list in Excel

SUBTOTAL function: A function that calculates a subgroup in a book set or a data table depending on the calculation involving the first parameter.

Syntax: SUBTOTAL (function_num, ref1, ref2, .) .

Inside:

- function_num: Number specifying the function used to calculate. Conventions are as follows: With Office version 2000 onwards the function convention is from number 1 to 11, with Office 2003, 2007 and later, the convention number is from 101 to 111. The numbers correspond to the functions:

+ AVERAGE function: 1 or 101.

+ COUNT function: 2 or 102.

+ COUNTA function: 3 or 103.

+ MAX function: 4 or 104.

+ MIN function: 5 or 105.

+ PRODUCT function: 6 or 106.

+ STDEV function: 7 or 107.

+ STDEVP function: 8 or 108.

+ SUM function: 9 or 109.

+ VAR function: 10 or 110.

+ VARP function: 11 or 111

- ref1, ref2 : Range of data range you want to reference, can contain from 2 to 254 ranges of data you want to calculate.

Attention:

- In case if there are many nested SUBTOTAl functions put in ref1, ref2 => nested SUBTOTAL functions are ignored to avoid double counting.

- The Function_num argument if it is in the value set from 1 to 11 => the function that performs the calculation includes the hidden values ​​in the dataset, if it is in the value set from 101 to 111 => the function that performs the calculation Ignore hidden values.

- The function will ignore without calculating all hidden rows by using the Auto Filter command which does not depend on the Function_num parameter.

- The function is set to calculate for vertical columns, it does not perform horizontal calculations.

- The function only performs calculations for 2D parameters, for example 3 D parameters = Sum (Sheet1: Sheet5! A5 => error function #VALUE!

- For Excel 2000 / XP Function arguments only accept values ​​from 1 to 11.

- In case if the data area has used Filter, when using Subtotal function, the result is not correct.

Example 1: For the data table.

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 1

Calculate total sales, average amount per item, calculate number of items sold based on number of customers.

- Calculate the total amount sold :

The sum function is the Sum function corresponding to Function = 9 in Subtotal => enter the formula in the cell to be calculated as follows: = SUBTOTAL (9, D13: D17) => get the results:

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 2

- Calculate the average amount per item .

The Average function corresponds to the function_num = 1 in the subtotal function => enter the formula SUBTOTAL (1, D13: D17) => get the result:

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 3

- Calculate the number of items sold based on the number of buyers .

The Count function corresponds to Function_num = 2 in Subtotal => enter the formula = SUBTOTAL (2, D13: D17) => get the result.

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 4

This way you can use the flexible Subtotal function in many calculations without having to remember other functions.

Example 2:

Using the Subtotal function is a built-in tool built into Excel.

Give the following table:

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 5

Calculate the total amount, the number of items purchased by each customer, and the total amount and total number of items sold by the store.

Step 1 : Select the entire data table (including the title) -> Go to Data tab -> Outline -> Subtotal . (In case you do not customize the toolbar you go to Data tab -> Subtotal ).

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 6

Step 2: The Subtotal dialog box appears:

- Item At each change in: select the column Title KH name because here calculated by total by customer name.

- Use function : Select Sum because here perform the sum should use Sum function.

- Item Add subtotal to : tick the Amount and Amount because here performs the total calculation by number and amount.

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 7

Click OK to get the results:

SUBTOTAL function - The calculation function for a group in a list in Excel Picture 8

So each customer is charged with the total and the total number of items purchased. Also calculate the total amount and the total number of items sold by the store. => Use more Sutotal feature and Subtotal function very suitable for managing sales.

Good luck!