In this comprehensive guide, we will explore the SUBTOTAL function in Excel, which is a versatile and powerful formula that allows you to perform various calculations on a range of cells while automatically excluding hidden rows. The SUBTOTAL function is particularly useful when working with large datasets, as it enables you to quickly summarize data and perform calculations without having to manually exclude hidden rows or filtered data.
SUBTOTAL Syntax
The syntax for the SUBTOTAL function in Excel is as follows:
SUBTOTAL(function_num, ref1, [ref2], …)
Where:
- function_num is a number that specifies the function to be used in the calculation. This can be any number between 1 and 11, or between 101 and 111. The numbers 1-11 include hidden rows in the calculation, while the numbers 101-111 exclude hidden rows.
- ref1, ref2, … are the cell references or ranges on which the specified function will be performed. You can include up to 254 references in the SUBTOTAL function.
SUBTOTAL Examples
Let’s look at some examples of how to use the SUBTOTAL function in Excel:
Example 1: Summing a range of cells
To calculate the sum of a range of cells (A1:A10) using the SUBTOTAL function, you would use the following formula:
SUBTOTAL(9, A1:A10)
This formula will return the sum of the values in the range A1:A10, including any hidden rows.
Example 2: Averaging a range of cells excluding hidden rows
To calculate the average of a range of cells (B1:B20) while excluding hidden rows, you would use the following formula:
SUBTOTAL(101, B1:B20)
This formula will return the average of the values in the range B1:B20, excluding any hidden rows.
Example 3: Counting the number of cells with data
To count the number of cells with data in a range (C1:C30), you would use the following formula:
SUBTOTAL(2, C1:C30)
This formula will return the count of cells with data in the range C1:C30, including any hidden rows.
SUBTOTAL Tips & Tricks
Here are some tips and tricks to help you get the most out of the SUBTOTAL function in Excel:
- Remember that the function_num argument determines whether hidden rows are included or excluded in the calculation. Use numbers 1-11 to include hidden rows, and 101-111 to exclude them.
- The SUBTOTAL function can be used with other functions such as SUM, AVERAGE, COUNT, MAX, MIN, and more. Simply change the function_num argument to the corresponding number for the desired function.
- When using the SUBTOTAL function with filtered data, it will automatically exclude any rows that are hidden due to the filter.
- If you want to perform multiple calculations on the same range of cells, you can use multiple SUBTOTAL functions in the same formula. For example, to calculate the sum and average of a range of cells, you could use the following formula:
=SUBTOTAL(9, A1:A10) & ” / ” & SUBTOTAL(1, A1:A10)
Common Mistakes When Using SUBTOTAL
Here are some common mistakes to avoid when using the SUBTOTAL function in Excel:
- Using the wrong function_num argument: Make sure to use the correct number for the desired function and whether you want to include or exclude hidden rows.
- Not specifying a range of cells: The SUBTOTAL function requires at least one cell reference or range as an argument. Make sure to include the appropriate references in your formula.
- Using SUBTOTAL with non-contiguous ranges: While the SUBTOTAL function can handle multiple cell references, it is not designed to work with non-contiguous ranges. In such cases, consider using separate SUBTOTAL functions for each range and then combining the results as needed.
Why Isn’t My SUBTOTAL Working?
If your SUBTOTAL function isn’t working as expected, consider the following troubleshooting steps:
- Check the function_num argument: Ensure that you are using the correct number for the desired function and whether you want to include or exclude hidden rows.
- Verify the cell references or ranges: Make sure that the cell references or ranges in your formula are correct and include the data you want to calculate.
- Ensure that the data is in the correct format: The SUBTOTAL function may not work correctly if the data in the specified range is not in the appropriate format (e.g., numbers stored as text). Check your data and correct any formatting issues as needed.
- Look for errors in the source data: If there are errors in the cells included in the SUBTOTAL function, the formula may not return the expected result. Fix any errors in the source data and try the formula again.
SUBTOTAL: Related Formulae
Here are some related formulae that you may find useful when working with the SUBTOTAL function in Excel:
- SUM: The SUM function calculates the sum of a range of cells. Use the SUBTOTAL function with function_num 9 or 109 to achieve the same result while including or excluding hidden rows, respectively.
- AVERAGE: The AVERAGE function calculates the average of a range of cells. Use the SUBTOTAL function with function_num 1 or 101 to achieve the same result while including or excluding hidden rows, respectively.
- MAX: The MAX function returns the maximum value in a range of cells. Use the SUBTOTAL function with function_num 4 or 104 to achieve the same result while including or excluding hidden rows, respectively.
- MIN: The MIN function returns the minimum value in a range of cells. Use the SUBTOTAL function with function_num 5 or 105 to achieve the same result while including or excluding hidden rows, respectively.
- AGGREGATE: The AGGREGATE function is similar to the SUBTOTAL function but offers more options for handling errors and nested SUBTOTAL or AGGREGATE functions. Consider using AGGREGATE if you need more advanced functionality than SUBTOTAL provides.
By understanding the SUBTOTAL function and its various applications, you can efficiently summarize and analyze large datasets in Excel while automatically accounting for hidden rows and filtered data. With this comprehensive guide, you should now be well-equipped to use the SUBTOTAL function effectively in your Excel projects.