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.
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:
- 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:
- 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.
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:
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 ).
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.
Click OK to get the results:
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!