Using SUBTOTAL Excel function
Let’s talk about benefits of using SUBTOTAL Excel function. This function offers too many features to be overlooked. On the surface, it appears that this function does nothing more than its name suggests: calculates a subtotal of your references: constants, individual cells, or ranges of cells. The reality is that this versatile function calculates 11 different subtotals, (think 11 functions in one): AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, VARP. This function works similarly to your typical SUM or AVERAGE, where you need to reference your data range to be used in the calculation, however, you also need to specify function type. As you are typing the name of this function in your formula bar, Excel will automatically display the list of possible function types, or you can look up function types from the table below. The function’s syntax is very simple: = SUBTOTAL(FUNCTION_TYPE, RANGE)
Another advantage of incorporating SUBTOTAL function in your Excel solution is that it will perform calculations for filtered values. Imagine that you are offering two Excel classes on your corporate website: “Excel for Beginners” and “Advanced Excel.” In addition, you recently made the Beginners class available on Udemy. Since you are new to their platform, you also signed up for a promotional program, which allows you to offer this class at 75% discount, as well as regular price of $ 199.99. Net revenue allows for Udemy’s 50% take on your course sales. Using the SUM or AVERAGE function will not calculate correct amounts for platform type, while SUBTOTAL is flexible enough to make this calculation:
If you have a hidden row that you need to ignore in your calculations, using three-digit function number type in SUBTOTAL will accomplish this task easily. Think about all of those helper rows you can create without worrying that their values will be part of your calculations… To change the example above, you would simply substitute function number of 1 with 101 for your AVERAGE calculation, and change 9 to 109 in your SUM.
If you are not sold on this function yet, imagine that you can use it to avoid double-counting values that occurs while using the SUM function in your columns or rows. SUBTOTAL fixes this problem for us, since one can actually use the SUBTOTAL function within the range of another SUBTOTAL function and its value will be ignored. This is precisely the reason, why Excel developers rely on SUBTOTAL function for calculating your outline subtotals, instead of using the SUM function. To see this in action, simply go to the Data menu, and select Subtotal in the Outline ribbon.
You can download Excel workbook with the examples described. Do you already use SUBTOTAL function in your Excel solutions, or are you planning to start using it after reading this post?
