Solved

summing the return on a filtered Xl column

Posted on 2014-11-13
8
71 Views
Last Modified: 2014-11-29
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
Comment
Question by:intelogent
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 125 total points
Comment Utility
You could try SUBTOTAL,

=SUBTOTAL(9,B:B)

or you could use the filter criteria in SUMIF/SUMIFS or SUMPRODUCT.
0
 

Author Comment

by:intelogent
Comment Utility
can u translate what this is doing....   =SUBTOTAL(9,B:B)
also where would i place this formula
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 125 total points
Comment Utility
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
 

Author Comment

by:intelogent
Comment Utility
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
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
Comment Utility
When the filter was applied, only data down to row 3355 was selected, leaving 3356 out of the filter's range.
0
 

Author Comment

by:intelogent
Comment Utility
.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Pivot help 3 21
Excel - Row Height +1 VBA 2 23
cannot get subtotal to work 8 17
Gantt chart 2 11
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now