About MrExcel Consulting Services Learn Excel Resources Challenge of the Month MrExcel Seminars Message Board MrExcel Store Podcast Search Media Contact Home

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.
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.