# 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
``````
-

## migrated from stackoverflow.comNov 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:

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

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

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