Totalling up a Filtered List
 

I am trying to figure out how to total up a filtered list in Excel.

I have used up to 3 filter conditions which when use will correctly only
show the entries filtered and will hide the rest.

For arguements sake, lets say that the first 3 columns contained data on
locations (North/South/East/West), Age range(10-20, 20-30 etc), and sex
(male/female). In the 4th column is a score/rating. In an unfiltered list
there are over 500 rows with data. I can quite easily put a SUM() formula at
the top to add up the scores.

When a filter is applied I need that SUM() to only total the scores of, say,
females in North, aged 10-20.

Is there an easy way to do this?

Thanks for your help.

Totalling up a Filtered List
 

The SUBTOTAL function ignores hidden items in a filtered list.

=SUBTOTAL(Func_Num,Range)

The first argument indicates the type of calculation you want:
Func_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"B Baggins" wrote:

I am trying to figure out how to total up a filtered list in Excel.

I have used up to 3 filter conditions which when use will correctly only
show the entries filtered and will hide the rest.

For arguements sake, lets say that the first 3 columns contained data on
locations (North/South/East/West), Age range(10-20, 20-30 etc), and sex
(male/female). In the 4th column is a score/rating. In an unfiltered list
there are over 500 rows with data. I can quite easily put a SUM() formula at
the top to add up the scores.

When a filter is applied I need that SUM() to only total the scores of, say,
females in North, aged 10-20.

Is there an easy way to do this?

Thanks for your help.

Totalling up a Filtered List
 

Fantastic - I was working on the sumif() with additional columns with
True/False for each criteria. I was hoping that there was an easier way.
Many thanks

"Ron Coderre" wrote:

The SUBTOTAL function ignores hidden items in a filtered list.

=SUBTOTAL(Func_Num,Range)

The first argument indicates the type of calculation you want:
Func_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"B Baggins" wrote:

I am trying to figure out how to total up a filtered list in Excel.

I have used up to 3 filter conditions which when use will correctly only
show the entries filtered and will hide the rest.

For arguements sake, lets say that the first 3 columns contained data on
locations (North/South/East/West), Age range(10-20, 20-30 etc), and sex
(male/female). In the 4th column is a score/rating. In an unfiltered list
there are over 500 rows with data. I can quite easily put a SUM() formula at
the top to add up the scores.

When a filter is applied I need that SUM() to only total the scores of, say,
females in North, aged 10-20.

Is there an easy way to do this?

Thanks for your help.