# summing the return on a filtered Xl column

hi,
I am using Xl 10.   I have a worksheet with several thousand rows.
if I filter a column, rows only appear meeting the criteria.
however, when I try to sum a column which is filtered, I can not get an answer. the formula stops blank.
I notice the rows are a weird progression of numbers as many are skipped ( I would expect this)  but how do I get totals for the columns
Microsoft Excel

Last Comment
intelogent
Norie

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
intelogent

can u translate what this is doing....   =SUBTOTAL(9,B:B)
also where would i place this formula
SOLUTION
Professor J

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Rob Henson

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Norie

In the formula I posted the 9 tells Excel to sum, there are various other options, eg count (1), average(2) etc, you'll get a full list via Intellisense when you enter the formula, or you can find it in the Help files.

B:B is simply the range to sum which you would of course change to suit your needs.
intelogent

really i find the powers of XL a never ending tool that i need to constantly get more of.

=SUBTOTAL(9,D2:D3355)     This expression was placed in the column of row 3356.

what i find fascinating and i would appreciate a comment on , is how this appears in all filtered results.
for instance if i am filtering on column B   yet the row of this formula has no value in column B, the formulas' result is shown are still shown.

what is intellisense
SOLUTION
Rob Henson

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
intelogent

.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY