Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #3  
Old 04-30-2014, 08:33 AM
gebobs gebobs is offline Formula Help Windows 7 64bit Formula Help Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

There's more than one way to skin this cat.

=SUMIF(F2:F214,"LSP",C2:C214)

Another way to do it, and somewhat more elegantly, is to Format As Table (Home tab). Make sure you check "My table has headers". This automatically formats the table according to whichever preset you choose. But more importantly, it creates a named table range allowing so much more functionality that I do it routinely with every table I make.

Once that is done, with a cell within the table selected, a new tab (Table Tools|Design) appears. In Table Style Options, click the checkbox for Total Row. This automatically adds a row to the bottom of the table. The leftmost cell will automatically have "Total" in it and another will have a calculation. You can easily change these to suit your needs. You will notice a "drop down" arrow when you select any of these cells. Click it and you will get a drop down box listing several functions you can use, including count, sum, min, max, etc. Beneath the counts (your column C), select Sum.

Also, filtering is automatically setup for the table. As in the totals row, you will notice a "drop down" arrows in each of the header cells. Click it and you will see all kinds of filtering operations. If besides "LSP" you have "ABC", you can then deselect "ABC" and click OK. You will notice that your totals row calculations have automatically updated to total just those items you filtered for i.e. "LSP".

You can imagine what kind of flexibility you now have. You can filter over several values, filter over various text values (Begins with, ends with, etc.). If the field you're filtering has dates in it, that opens up other filtering options (this month, last week, before, etc.). If your field has numbers, there are number filters too (greater than, top 10, etc.)

You can also set up a pivot table easily to give you all kinds of summary information. In Table Design, click on Summarize with PivotTable and play around with it. See attached file for what I worked on.
Attached Files
File Type: xlsx Book1.xlsx (12.8 KB, 9 views)
Reply With Quote
 

Tags
excel 2010, formula



Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help WHEN formula agent007 Excel 5 02-06-2014 09:20 PM
Formula Help What formula should I use? TotalONE Excel 3 11-26-2013 01:30 PM
Formula Help help with formula? doczilla Excel 2 09-25-2011 04:14 PM
Formula Help PMT Formula OneOleGuy Excel 4 03-05-2011 09:54 AM
Formula Help If formula sixhobbits Excel 1 10-02-2009 08:02 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft