Applies to
Microsoft Office Excel 2003 Microsoft Excel 97, 2000, and 2002

This article was adapted from MrExcel.com. Visit the MrExcel.com Web site for more tips and information.

In this article

Method 1: Use creative IF statements in conjunction with Paste Special

Method 2: Use Advanced Filter to get the list of unique accounts

Method 3: Use the Consolidate command

Method 4: Use the Subtotal command

Method 5: Use a PivotTable report

Let's say you build a monthly transaction list in which each account code can occur multiple times. At the end of the month, you need to organize and subtotal the data by account code.

There are at least five ways to do this task. The following is a tutorial on the five methods.

 Note   The example data set has account numbers in column A and amounts in column B. The data range is A2:B100, and the data is not currently sorted.

Method 1: Use creative IF statements in conjunction with Paste Special

Here are the steps:

1. Sort the data by Account (column A).
2. Invent a formula in column C that will keep a running total for each account. For example, in cell C2:

        

   =IF(A2=A1,C1+B2,B2)

3. Invent a formula in column D that will identify the last entry for each account. For example, in cell D2:

        

   =IF(A2=A3,FALSE,TRUE)

4. Copy the formulas in C2:D2 down to all of your rows.
5. Copy C2:D100. While this range is still selected, click Paste Special on the Edit menu, click Values, and then click OK to change the formulas in C2:D100 to values.
6. Sort by column D, descending.
7. For the rows that have a TRUE in column D, you have a unique list of account numbers in column A, and the final running total for that account in column C.

Pros  It's quick. All you need is a keen sense of how to write IF statements.

Cons  There are better ways.

 Top of Page

Method 2: Use Advanced Filter to get the list of unique accounts

This is a method for getting a list of the unique account numbers:

1. Select the range A1:A100.
2. On the Data menu, point to Filter, and then click Advanced Filter.
3. In the Advanced Filter dialog box, click Copy to another location.
4. Select the Unique records only check box.
5. Pick a blank section of the worksheet where you want the unique list to appear, and type this location in the Copy to box.

    Note   The Copy to box remains dimmed and unavailable until you click Copy to another location.

6. Click OK.

   The unique account numbers will appear in the location you entered.

7. Enter any further manipulations, array formulas, and so forth that you need to get your results.

Pros  Quicker than Method 1. No sorting required.

Cons  The array formulas required after this will make your head spin.

 Top of Page

Method 3: Use the Consolidate command

This method uses the Consolidate command, which has a few requirements:

• Your account number has to be to the left of the numeric fields you want to total.
• You have to have headings above each column.
• You have to assign a range name to the rectangular block of cells that includes the account numbers along the left column and the headings along the top. In this case, that range is A1:B100.

Here is the method:

1. Highlight the range A1:B100.
2. Assign a range name to this area by clicking in the Name box (to the left of the formula bar), and then typing a name such as TotalMe. (Alternately, on the Insert menu, click Name.)
3. Put the cell pointer in a blank section of the worksheet.
4. On the Data menu, click Consolidate.
5. In the Consolidate dialog box, in the Reference box, type the range name (for example, TotalMe).
6. Under Use labels in, select both Top row and Left column.
7. Click OK.

Pros  No sorting required. You can do it with a series of keyboard shortcuts: ALT+D+N (rangename), ALT+T, ALT+L, ENTER. It is easily scalable. If your range includes 12 monthly columns, the answer will have totals for each month.

Cons  If you use the Consolidate command again on the same sheet, you need to clear the old range name out of the All references box by using the DELETE key. The account number has to be to the left of your numeric data. It is slightly slower than a PivotTable report, which becomes noticeable for datasets that have more than 10,000 records.

 Top of Page

Method 4: Use the Subtotal command

This is a cool feature. But because the resulting data is strange to work with, you may use it less often than Consolidate.

1. Sort by column A, ascending.
2. Select any cell in the data range.
3. On the Data menu, click Subtotals.

   By default, Excel offers to subtotal the last column of your data. That works in this example, but often you have to scroll through the Add subtotal to list to choose the correct fields.

4. Click OK.

   Excel inserts a new row at each change of account number and calculates a subtotal.

After you have the subtotals in, you will see small 1, 2, and 3 buttons appear below the name box. Click the 2 button to see just one line per account with the totals. Click the 3 button to see all lines.

Pros  Cool feature. Great for printing reports that have totals and page breaks after each section.

Cons  The data must be sorted first. Can be slow when there's lots of data. You have to use the Go To command (Edit menu) and then click Special to select only the visible cells so you can move the totals elsewhere. You have to use the Subtotals command (Data menu) and then click Remove All to get back to your original data.

 Top of Page

Method 5: Use a PivotTable report

PivotTable reports are the most versatile solution of all. Your data does not have to be sorted. The numeric columns can be to the left or right of the account number. You can easily have the account numbers go either down the page or across it.

1. Select any cell in the data range.
2. On the Data menu, click PivotTable and PivotChart Report.
3. In the PivotTable and PivotChart Wizard, click Next to accept the defaults in Step 1.
4. Make sure that the data range in Step 2 of the wizard is correct (it usually is), and then click Next.
5. Click the Layout button in Step 3. (Excel 97 users automatically go to Layout in Step 3.)
6. In the Layout dialog box, drag the Account button from the right side into the Row area.
7. Drag the Amount button from the right side into the Data area.
8. Click OK. (Excel 97 users click Next.)
9. Specify whether you want the results to be located in a new sheet or in a specified section of an existing sheet, and then click Finish.

Pros  Quick, flexible, powerful. Fast, even for lots of data.

Cons  Somewhat intimidating.

 Top of Page