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 (9), MAX (4) or AVERAGE (1).
Choose a Function
See how you can make it easy for people to change the Excel function with SUBTOTAL, to get a different result in their formula.
There are written steps, and a step-by-step video, below
SUBTOTAL Function Numbers
When you start typing the SUBTOTAL function, you’ll see this list of function numbers. You can type one of the numbers, or double-click on an item in the drop down list.
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.
Name the Lists
Then, name the list of numbers, as FuncNum.
Next, name the list of function names, as FuncList.
Create 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 Function List Cell
Next, name the cell with the drop down list of function names, 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 That Cell
Next, 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.
=SUBTOTAL(FuncNumSel,SalesTable[Qty])
- Note: If you’re not using an Excel table, you could use a cell reference as the second argument, instead of the table reference.
Then, copy the formula across to G2, and format the numbers, if necessary.
Choose a Function
Then, select a function name from the drop down list in cell C2
The total cells, in row 2, 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.
Video: Change Excel Function With SUBTOTAL
To see the steps for creating the interactive summary Subtotal formulas, and the drop down list, watch this short video tutorial.
____________
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?
Thanks,
Marcus
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
Hi i’m looking to see how I can create a drop down list where I have:
*Name *Date *Membership fee *Preference *Allergies *No of people in household *Engaging
Wayne 3rdfeb Yes Volunteer no 4 Indeed
I’m looking for help where if I select a drop down list of a 7 day gap the other selections on the right side of the date changes. For example if Wayne didn’t pay a fee on 10th Feb 2021 if I selected the date on the drop down list it would show me NO and the other data below the titles on the right hand side of the date would change. I’d be very grateful if you can help me.
very good for your efforts.
Best regards.