Sum Column, but only Filtered Rows

Question

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:

=SUMIF(A:A,A1,D3:D12982)    
=SUMIF(A:A,A1,G3:G12982)

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

Answer 1

The SUBTOTAL function should give you what you need.

Put a formula similar to this in cell A1:

=SUBTOTAL(9,A3:A12982)

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.

Answer 2

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.

see: http://office.microsoft.com/en-us/excel/HA010872271033.aspx

Answer 3

=SUBTOTAL(109,D3:D12982)

Syntax

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.


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

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