Excel SUBTOTAL Function
Unlike SUM, AVERAGE, COUNTA and other functions which only do one thing, the SUBTOTAL function is versatile and can perform different arithmetic and logical operations, such as calculating a sum or average, finding the minimum or maximum value, getting the standard deviation, and more (see the table below the argument function_num). Also, SUBTOTAL is more flexible since it can either include or exclude hidden values.
The SUBTOTAL function returns a numeric value.
- SUBTOTAL ignores values in filtered-out rows, regardless of function_num.
- SUBTOTAL with function_num of 101-111 ignores values in hidden rows, but not in hidden columns. Values in hidden columns are always included.
- SUBTOTAL ignores other SUBTOTAL formulas that exist in ref1, ref2, … to avoid double-counting.
- SUBTOTAL will return the #DIV/0! error if the function has to perform a division by zero (0).
- SUBTOTAL will return the #VALUE! error if:
- function_num is any number other than 1-11 or 101-111;
- Any of the ref arguments contains a non-numeric value;
- Any of the ref arguments contains a 3-D reference.
Supposing you have an inventory sheet as shown above, to get the total number of items in the sheet, please copy or enter the formula below in cell G5, and press Enter to get the result:
To get the total units of all the items in both Store I and Store II in the sheet, please copy or enter the formula below in cell G8, and press Enter to get the result:
To know the average price of the items in the sheet, please copy or enter the formula below in cell G11, and press Enter to get the result:
√ Note: To make the results show properly in currency format as shown above, you should apply the Format Cells feature: Select the cells where you want to apply the feature, then press Ctrl + 1. Under the tab Number in the pop-up Format Cells dialog, choose Currency, and set the decimal places, symbol and format for negative numbers in the right section: