Solved

MS Excel 2007 - Sum $ amount for my filtered selection.

Posted on 2012-04-06
2
283 Views
Last Modified: 2012-04-06
How do I add a cell to sum up the value that I select in a filter?

I have a list locations and $ amounts. Each location has its own dollar amount. The column headers for this data has the drop down filter turned on.  I want to add to my spread sheet a cell that sums the dollar values for the location that I select in from my filter.  If I select from my filter Albany I want the summary cell to only total Albany $ amounts. If I change to a new location the sum should change accordingly.

SUM: $926,000

Location      Amount            
Albany      $340,000
Albany      $123,000
Rensselaer  $456,234
Rensselaer $156,000
Saratoga      $340,000
Saratoga      $123,000
Albany      $340,000
Albany      $123,000
Rensselaer $456,234
Rensselaer $156,000

I know I can do this with a Piviot table  or insort subtotals into the data. My question is specific to summing data for a selected filter.

Thanks for your assistance
Ldufresne
0
Comment
Question by:Lou Dufresne
2 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37816270
Try SUBTOTAL.

=SUBTOTAL(9,B3:B12)

Where B3:B12 is the range with the dollar amounts.
0
 
LVL 1

Author Closing Comment

by:Lou Dufresne
ID: 37816620
Can you please tell me what the "9" is in the formula  =Subtotal(9,B3:B12)

Other than that the fornula works great.

Thanks
Ldufresne
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

24 Experts available now in Live!

Get 1:1 Help Now