# Thread: Total filtered items only

1. ## Total filtered items only

I have a worksheet which is set up like a data base and has data in each cell. the filter is on and the database can be filtered by various fields. I have a total for the whole data base however, when I filter for certain criteria, I want the total to only sum those items for the criteria selected. I also need a count on another criteria totaling the (criteria) filtered amount only.

Thank you

2. Use SUBTOTAL function. Function Num 1 = average, 2 = count
=subtotal(1, A1:A1000) for average of the filtered values in A1:A1000 or =subtotal(101, A1:A1000) to average filtered and non-hidden items

=subtotal(2, A1:A1000) for count of the filtered values in A1:A1000 (or = subtotal(102, A1:A1000)...

The complete list of function numbers
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Steve

3. To add on to Steve's good advice, and if you are working with Excel 2010, you can also use a brand new function called: Aggregate.

The Excel AGGREGATE function returns an aggregate in a list or database. The function can apply different aggregate calculations to a list or database with the option to specify whether hidden rows, error values and/or any nested Subtotal or Aggregate functions are ignored. To begin with, AGGREGATE has 19 functions, compared to SUBTOTAL’s 11. Another advantage is that AGGREGATE can also ignore other things such as errors and nested SUBTOTAL functions in addition to the hidden data.

4. Thank you all

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•