Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

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
0
intelogent
Asked:
intelogent
  • 3
  • 2
  • 2
  • +1
4 Solutions
 
NorieData ProcessorCommented:
You could try SUBTOTAL,

=SUBTOTAL(9,B:B)

or you could use the filter criteria in SUMIF/SUMIFS or SUMPRODUCT.
0
 
intelogentAuthor Commented:
can u translate what this is doing....   =SUBTOTAL(9,B:B)
also where would i place this formula
0
 
ProfessorJimJamCommented:
if you have excel 2010 then nothing beats off the cool function of AGGREGATE

assuming your data starts from column A then put =AGGREGATE(9,5,A:A)  and then drag to the left
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob HensonIT & Database AssistantCommented:
If using a SUM, it will include the hidden rows and normally SUM will ignore non-numerical values.

Are you putting the SUM in the same column that is being summed and summing the whole column? if so this will create a Circular Reference and default answer for CR formulas is zero.

Using the SUBTOTAL function as suggested above will only sum the visible rows, but if you use whole column (B:B in above example) and put the formula in the same column, it will go circular again.

Try using SUBTOTAL as suggested but put in a different column.

Thanks
Rob H
0
 
NorieData ProcessorCommented:
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.
0
 
intelogentAuthor Commented:
really i find the powers of XL a never ending tool that i need to constantly get more of.

your answer works... and explains what another has done...

=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
0
 
Rob HensonIT & Database AssistantCommented:
When the filter was applied, only data down to row 3355 was selected, leaving 3356 out of the filter's range.
0
 
intelogentAuthor Commented:
.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now