Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count rows in a filtered list when using AutoFilter? | Excel Worksheet Functions | |||
Drop-Down List Choice Affects Multiple Cells? | Excel Worksheet Functions | |||
creating a filtered list | Excel Discussion (Misc queries) | |||
Subtotals in a list | Excel Discussion (Misc queries) | |||
Determine Frequency in Filtered List | Excel Worksheet Functions |