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:

=INDEX(FuncNum, MATCH(FunctionSel,FuncList,0))


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.


You may also like...

3 Responses

  1. Marcus says:

    I’m struggling to create a spreadsheet with a calculation function that should sum dollar values populated in a column based on the adjacent column which notes the category / type of the dollar value. Essential there are three main columns. Column “A” has standard columns titles from “A3 to A56”. Column “F” and “G” run from run 2 through 100. In column “F” I will enter dollar values. In column “G” I will select drop down options which mirror the titles in column “A”. I want the sum of each drop down type to be calculated and populated in the column “A” next to the respective category / type. This will be used to record receipts and tally the amounts spent per category. Can yo assist me with developing the proper formula to attach and calculate the dollar value of entries that are listed under a particular drop down category?



  2. Christina says:

    How did you get to put the formula to change the values when you changed the selected products and the quantity for the final cost to change

  1. September 6, 2012

    […] Excel Drop Down List By Debra Dalgleish, on September 6th, 2012 Last year, I shared a technique for selecting a function name from a drop down list, and that changed the formulas in a summary row on the worksheet. Choose MAX, […]

Leave a Reply

Your email address will not be published. Required fields are marked *