Sum Column, but only Filtered Rows

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

CheeseConQueso

Posted 2009-01-23T16:51:59.370

Reputation: 290

Answers

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.

Robert Mearns

Posted 2009-01-23T16:51:59.370

Reputation: 404

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 – None – 2009-02-05T16:07:45.380

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 – None – 2009-02-05T16:11:24.937

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 – 2010-11-02T12:55:17.667

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

Tyndall

Posted 2009-01-23T16:51:59.370

Reputation: 184

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 – None – 2009-01-27T22:01:20.203

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 – 2009-01-27T22:05:41.550

=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

CheeseConQueso

Posted 2009-01-23T16:51:59.370

Reputation: 290