Buzz
In Excel, we have built-in calculation functions such as SUM, AVERAGE, and MIN for data analysis. If you want to calculate subtotals, you can use the SUBTOTAL function along with the mentioned functions. You can use up to 11 functions within the SUBTOTAL function, each corresponding to a different formula.
Tips for using the SUBTOTAL function in Excel
How to use the SUBTOTAL function in Excel?
1. SUBTOTAL function syntax
=SUBTOTAL(function_num, ref1, ref2,...)
Parameters:
+ Function_num: numbers 1 to 11 (or 101 to 111) determine which function is used for calculation in the SUBTOTAL operation. You can refer to the code table for Function_num below.
+ Ref1, ref2: are the reference ranges where you want to perform the subtotal calculation.
The Function_num codes are as follows:
Therefore, you just need to substitute numbers from 1 to 11 (or 101 to 111) into the SUBTOTAL function syntax, and the SUBTOTAL function will operate similarly to that function.
If you use 1-11 for the function_num argument, the SUBTOTAL function will calculate hidden values as well, whereas using 101-111 will not include hidden values.
Using a value other than 1-11 or 101-111 will result in an error #VALUE! in Excel.
2. Examples of using the SUBTOTAL function in Excel
Example 1: Calculate the average of the selected range
Suppose you have an Excel spreadsheet with pre-entered data as shown below and you want to calculate the average of values, follow these steps:
Click on an empty cell and select the formula bar. Enter the syntax of the SUBTOTAL function into the formula bar, starting with =SUBTOTAL( .
Next is the function_num argument corresponding to an Excel function. In this example, we will use the Average function to calculate the average of values in the selected range, excluding hidden values. The Average function corresponds to 101, so we enter =SUBTOTAL(101 .
Then, we specify the range for calculating the average of values. In this example, we will calculate the sum of values in the range from A1 to A10.
Therefore, the complete syntax for the SUBTOTAL function is =SUBTOTAL(101,A1:A10).
The result will be similar to the image below:
If you want to calculate subtotals for multiple ranges, you add the ranges separated by commas after the function_num argument.
Example 2: Count non-empty cells in the spreadsheet
Suppose you have an Excel spreadsheet with data as shown below.
To count non-empty cells to determine the number of employees who have submitted data links, follow these steps:
Click on an empty cell in the Excel sheet and select the formula bar.
Enter the syntax of the SUBTOTAL function into the formula bar, starting with =SUBTOTAL(.
In this example, we need to count non-empty cells, so we will choose the COUNTA function, corresponding to number 3. Therefore, function_num will be 3, ref1 = B2:B8 is the range to calculate subtotal.
Thus, the complete syntax will be =SUBTOTAL(3,B2:B8)
The final result will be as follows:
Additionally, readers can also use the SUM function to calculate totals quickly and accurately.
