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!

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!

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!

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!

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!