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.

subtotal05b

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

Then, name the list of numbers, as FuncNum.

subtotalselect03

Name the list of function names, as FuncList.

subtotalselect04

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.

subtotalselect05

Name the cell with the drop down list, 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 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])

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.

subtotalselect09

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

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

    Thanks,

    Marcus

  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 *