Change Excel Function With SUBTOTAL
We looked at the Excel SUBTOTAL function on Friday, and saw how it works with hidden rows. The first argument tells Excel which function you want to use, such as SUM, MAX or AVERAGE.
Change the Function
Instead of typing the first argument in a SUBTOTAL formula, you could make it more flexible, by allowing users to select the function they want. In this example, we’ll set up total formulas, and users can change the function at any time.
Set Up the Named Excel Table
In this example, there are sales orders, in an Excel table named SalesTable. We’ll create totals for the Qty, Cost and Amt columns in that table.
List the SUBTOTAL Functions
In the Excel Help files, if you search for SUBTOTAL, you’ll find a table of the functions and their numbers. If you’re using Excel 2003, or later version, you can use the 100 series number, which ignore both filtered and manually hidden values.
Copy that table to a sheet in the Excel workbook, and delete any functions that you don’t need.
Then, name the list of numbers, as FuncNum.
Name the list of function names, as FuncList.
Create a Drop Down List of Functions
On the sales order sheet, above the Excel table, create a drop down list of functions, by using a data validation list.
Name the cell with the drop down list, as FunctionSel.
Find the Function Number
On the worksheet with the lists, we’ll use the INDEX function to determine which function number was selected.
In cell E3, enter this INDEX function, to find the selected function name, and return the matching function number:
Name the function number cell as FuncNumSel.
Create the Subtotal Formulas
In cell E2 on the sales order sheet, enter a subtotal function that uses the selected function number as its first argument.
If you’re not using an Excel table, you could use a cell reference as the second argument, instead of the table reference.
Copy the formula across to G2, and format the numbers, if necessary.
Then, select a function name from the drop down list in cell C2, and the total cells will automatically change, to show the subtotal for the selected function.
Download the Sample File
To see the SUBTOTAL function, and the lists, you can download the Flexible Subtotal Function sample file. The file is in Excel 2007 format, and is zipped. There are no macros in the workbook.
Watch the Video
To see the steps for creating the interactive summary Subtotal formulas, and the drop down list, please watch this short video tutorial.