Four Ways To Summarize Data
Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada. The book shown in this show is Learn Excel from MrExcel. You have a dataset with customer, date, HW Sales, SW Sales, Total. There are thousands of rows. You would like to get totals by customer. Today's episode shows four ways for getting the totals.
Method 1: SUMIF
1. Copy the Customer heading to a blank cell G1.
2. From the menu, Data - Filter - Advanced Filter.
3. In the Advanced Filter dialog, choose Copy to New Location, Unique Values. Specify G1 as the output range
This will give you a unique list of customers in column G.
4. The formula in H2 is =SUMIF($A$2:$A$3946,G2,$E$2:$E$3946) 5. Copy the formula down to the other rows in column H. Results:
Method 2: Subtotals
1. Choose a single cell in column A.
2. Click the AZ sort button in the standard toolbar.
3. From the menu, select Data - Subtotals. Fill out the dialog like this:
4. Press the "2" group and outline button to show a summary.
Method 3: Consolidate
1. Select a blank cell to the right of your data.
2. From the menu, Data - Consolidate.
3. Fill out the Consolidate dialog as follows:
The result: For each unique customer in the left column of your data, you get one row. Excel sums all numeric data, which in this case includes the date field. You will have to delete the date field.
Method 4: Pivot Table
1. Select a single cell in your data. From the menu, Data - Pivot Table and PivotChart Report.
2. Click Finish.
3. Click on Customer. Click Add to Row Area.
4. Click on Total. Click on Add to Data Area.
The pivot table is complete.
Excel is a great product because there are many ways to accomplish any task. As shown here, there are four ways to produce totals by customer! For the BEST TV show on technology, check out Call for Help. This tip was originally published on April 13, 2006 and aired on TechTV in Canada and Australia on April 13, 2006. The permanent URL for this page is http://www.mrexcel.com/tip124.shtml. If you are looking for show notes from another episode, visit my complete list of TechTV appearances.
MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
Excel is a registered trademark
of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.
All contents Copyright 1998-2008 by MrExcel Consulting.