Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a large flat file of a bunch of ungrouped primary key values.

I want to filter on a key and populate cell A1 with the value selected from the dropdown list.

In the end, I want to see a sum of each primary key's associated value in column D & G after filtering on that primary key.

I'm using this function at the top of row D and G respectively:


Does anyone know, through using a function, not a macro, how to populate a cell with a filter's selected value?

Sample data & solution:

                             D                        G

                             7                        6
  id      class   trnsfr  reg_hrs   crs_no    sec  crs_hrs
6181191      BT     R        4      INIS210    1      3
6181191      ED     G        3      COMS223    4      3
share|improve this question

migrated from Nov 2 '10 at 11:37

This question came from our site for professional and enthusiast programmers.

The SUBTOTAL function should give you what you need.

Put a formula similar to this in cell A1:


The function number of 4 in the SUBTOTAL function tells it to use the MAX function.

This works because unlike the MAX function, the SUBTOTAL function ignores any rows that are not displayed in the result set of a filter.

share|improve this answer
interesting... but it gives me 6 as the result... i am looking for the sum, not the max... you know what number 1-11 that is? im going to google it now – CheeseConQueso Feb 5 '09 at 16:07
its 9.. and it worked... thanks rob – CheeseConQueso Feb 5 '09 at 16:08
edit your answer to 9 so i can credit you with the correct response! thanks, i never ran into this function and its usefulness is apparent to me now – CheeseConQueso Feb 5 '09 at 16:11
Also, on the Data tab in Excel 2007 there is a Subtotal button which makes setting the formula up a little easier. Its been a while since I've used 2003 but something similar may exist in the menus. – Xantec Nov 2 '10 at 12:55

Try a SUM(IF( Array formula. They are more powerful.

Remember every time you edit an Array formula you must re-enter it with CTRL+SHIFT+ENTER not just ENTER.


share|improve this answer
I'd have to name all my ranges, no? Either way, the real bite is a command I don't know that probably exists that equates to the filtered value of a drop down... Or a combination of cell properties that can be accessed via a built in function. I'm going to keep looking for the answer, but thanks – CheeseConQueso Jan 27 '09 at 22:01
You don't have to name your ranges with the Array formulas. When you say filter, do you mean an Advanced Data Filter in excel? – Tyndall Jan 27 '09 at 22:05


SUBTOTAL(function_num, ref1, ref2, ...)

> Function_num is the number 1 to 11 (includes hidden values) 
> or 101 to 111
> (ignores hidden values) that specifies
> which function to use in calculating
> subtotals within a list.

(includes hidden values) 
    1 AVERAGE 
    2 COUNT 
    3 COUNTA 
    4 MAX 
    5 MIN 
    6 PRODUCT 
    7 STDEV 
    8 STDEVP 
    9 SUM 
    10 VAR 
    11 VARP 

(ignores hidden values) 
    101 AVERAGE 
    102 COUNT 
    103 COUNTA 
    104 MAX 
    105 MIN 
    106 PRODUCT 
    107 STDEV 
    108 STDEVP 
    109 SUM 
    110 VAR 
    111 VARP
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .