Summing a filtered list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
=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
 
Hi Peo

This has baffled me for weeks. You've cracked it in two minutes. Nice one!

Many thanks

Neil
 
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.
 
Back
Top