| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing a filtered list
Hi
I have a list of data. I'm filtering a one of the columns to find all records with a value between x and y. This results in only the matching records being displayed. Real simple. Now, is there an instruction to analyse ONLY the records that are remaining? Example: I'd like a cell at the end saying "# records matching criteria", and another cell summing a particular column (again using ONLY the remaining records). At present, I'm doing this using macros to filter, cut, paste, etc. It's well slow! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing a filtered list
=SUBTOTAL(3,Full_Range)
(where full range is the unfiltered range) will count visible text and numbers, replace 3 with 2 and it will count visible numbers, replace it with 9 and it will sum visible numbers So you could use =SUBTOTAL(3,Full_Range)&" records matching criteria" and =SUBTOTAL(9,Full_Range) -- Regards, Peo Sjoblom "Neil" wrote in message ... Hi I have a list of data. I'm filtering a one of the columns to find all records with a value between x and y. This results in only the matching records being displayed. Real simple. Now, is there an instruction to analyse ONLY the records that are remaining? Example: I'd like a cell at the end saying "# records matching criteria", and another cell summing a particular column (again using ONLY the remaining records). At present, I'm doing this using macros to filter, cut, paste, etc. It's well slow! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing a filtered list
Hi Peo
This has baffled me for weeks. You've cracked it in two minutes. Nice one! Many thanks Neil "Peo Sjoblom" wrote: =SUBTOTAL(3,Full_Range) (where full range is the unfiltered range) will count visible text and numbers, replace 3 with 2 and it will count visible numbers, replace it with 9 and it will sum visible numbers So you could use =SUBTOTAL(3,Full_Range)&" records matching criteria" and =SUBTOTAL(9,Full_Range) -- Regards, Peo Sjoblom "Neil" wrote in message ... Hi I have a list of data. I'm filtering a one of the columns to find all records with a value between x and y. This results in only the matching records being displayed. Real simple. Now, is there an instruction to analyse ONLY the records that are remaining? Example: I'd like a cell at the end saying "# records matching criteria", and another cell summing a particular column (again using ONLY the remaining records). At present, I'm doing this using macros to filter, cut, paste, etc. It's well slow! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing a filtered list
Thanks for the feedback
-- Regards, Peo Sjoblom "Neil" wrote in message ... Hi Peo This has baffled me for weeks. You've cracked it in two minutes. Nice one! Many thanks Neil "Peo Sjoblom" wrote: =SUBTOTAL(3,Full_Range) (where full range is the unfiltered range) will count visible text and numbers, replace 3 with 2 and it will count visible numbers, replace it with 9 and it will sum visible numbers So you could use =SUBTOTAL(3,Full_Range)&" records matching criteria" and =SUBTOTAL(9,Full_Range) -- Regards, Peo Sjoblom "Neil" wrote in message ... Hi I have a list of data. I'm filtering a one of the columns to find all records with a value between x and y. This results in only the matching records being displayed. Real simple. Now, is there an instruction to analyse ONLY the records that are remaining? Example: I'd like a cell at the end saying "# records matching criteria", and another cell summing a particular column (again using ONLY the remaining records). At present, I'm doing this using macros to filter, cut, paste, etc. It's well slow! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing a filtered list
If you have defined the 'list' then Excel provides inbuilt functionality to
aggregate the data in the list. On the List toolbar, there is a "toggle total row" that can be turned on. At the bottom of the list, then you can add Sum, Count, Average, etc. from a drop down. This total row is automatically updated for the filter everytime you change the filter. "Peo Sjoblom" wrote: Thanks for the feedback -- Regards, Peo Sjoblom "Neil" wrote in message ... Hi Peo This has baffled me for weeks. You've cracked it in two minutes. Nice one! Many thanks Neil "Peo Sjoblom" wrote: =SUBTOTAL(3,Full_Range) (where full range is the unfiltered range) will count visible text and numbers, replace 3 with 2 and it will count visible numbers, replace it with 9 and it will sum visible numbers So you could use =SUBTOTAL(3,Full_Range)&" records matching criteria" and =SUBTOTAL(9,Full_Range) -- Regards, Peo Sjoblom "Neil" wrote in message ... Hi I have a list of data. I'm filtering a one of the columns to find all records with a value between x and y. This results in only the matching records being displayed. Real simple. Now, is there an instruction to analyse ONLY the records that are remaining? Example: I'd like a cell at the end saying "# records matching criteria", and another cell summing a particular column (again using ONLY the remaining records). At present, I'm doing this using macros to filter, cut, paste, etc. It's well slow! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Getting data from a filtered list | Excel Discussion (Misc queries) | |||
| Filtered Validation List | Excel Worksheet Functions | |||
| Totalling up a Filtered List | Excel Worksheet Functions | |||
| summing values appearing in col B when col A has been filtered | Excel Worksheet Functions | |||
| Using TRIMEAN on a filtered list | Excel Discussion (Misc queries) | |||
Linear Mode
