Change Excel Function With SUBTOTAL

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 (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.

list of function numbers
list of function numbers

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.

subtotalselect05

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.

subtotalselect00

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.

subtotalselect01

Copy that table to a sheet in the Excel workbook, and delete any functions that you don’t need.

subtotalselect02

Name the Lists

Then, name the list of numbers, as FuncNum.

subtotalselect03

Next, name the list of function names, as FuncList.

subtotalselect04

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.

subtotalselect05

Name Function List Cell

Next, name the cell with the drop down list of function names, as FunctionSel.

subtotalselect06

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))

subtotalselect07

Name That Cell

Next, name the function number cell, as FuncNumSel.

subtotalselect08

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.

subtotalselect09

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.

____________

5 thoughts on “Change Excel Function With SUBTOTAL”

  1. 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

  2. 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

  3. 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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.