Data Associated with Blank Cells May Be Excluded from Subtotals (164499)

CAUSE

This problem occurs when the following conditions are true:

You click a cell in a list that contains blank cells and click Subtotals on the Data menu. -and-
In the "At each change in" box in the Subtotal dialog box, you specify a column that contains blank cells. -and-
You select Sum in the Use Function list, clear the Summary Below Data check box, and click OK.

When you do not clear the Summary Below Data check box, Microsoft Excel 5.0 and 7.0 includes data for the blank cells in the summary function. The value that Microsoft Excel associates with a blank cell is the value for the preceding cell that is not blank.

NOTE: The correct behavior is for Microsoft Excel to ignore the values for blank cells when you use the Subtotals command regardless of whether you select the Summary Below Data check box.

RESOLUTION

To resolve this behavior, type values in the blank cells.

For example, if you are using the following data

   A1: Name   B1: Amount
   A2: a      B2: 1
   A3:        B3: 1
   A4:        B4: 1
   A5: b      B5: 1
   A6:        B6: 1
   A7: c      B7: 1

type values for cells A3, A4, and A6 as follows: A1: Name B1: Amount A2: a B2: 1 A3: a B3: 1 A4: a B4: 1 A5: b B5: 1 A6: b B6: 1 A7: c B7: 1 When you click A1 and click Subtotal on the Data menu, Microsoft Excel calculates the following totals:

   A1:  Name        B1:  Amount
   A2:  a           B2:  1
   A3:  a           B3:  1
   A4:  a           B4:  1
   A5:  a Total     B5:  3
   A6:  b           B6:  1
   A7:  b           B7:  1
   A8:  b Total     B8:  2
   A9:  c           B9:  1
   A10: c Total     B10: 1
   A11: Grand Total B11: 6

MORE INFORMATION

Behavior in Microsoft Excel 5.0 and 7.0

In the Subtotal dialog box, if the field you are selecting in the "At each change in" list contains empty cells, Microsoft Excel ignores the rows that contain the empty cells when you clear the Summary Below Data check box. Example:

To see this behavior, use the following steps:

Type the following data in a new worksheet:

A1: Name B1: Amount A2: a B2: 1 A3: B3: 1 A4: B4: 1 A5: b B5: 1 A6: B6: 1 A7: c B7: 1

Select cell A1 and click Subtotals on the Data menu.
In the Subtotals dialog box, click Name in the "At each change in" list, click Sum in the Use Function list, and then click Amount in the Add Subtotal To list.

Click OK.

The data appears as follows:

      A1:  Name         B1:  Amount
      A2:  a            B2:  1
      A3:               B3:  1
      A4:               B4:  1
      A5:  a Total      B5:  3
      A6:  b            B6:  1
      A7:               B7:  1
      A8:  b Total      B8:  2
      A9:  c            B9:  1
      A10: c Total      B10: 1
      A11: Grand Total  B11: 6

NOTE: The blank cells in column A are associated with the value for the preceding nonblank cell in the column.

Select cell A1 and click Subtotals on the Data menu.

In the Subtotal dialog box, click to clear the Summary Below Data check box, and then click OK.

The data appears as follows:

      A1: Name         B1: Amount
      A2: Grand Total  B2:  6
      A3: a Total      B3:  1
      A4: a            B4:  1
      A5:  Total       B5:  3
      A6:              B6:  1
      A7:              B7:  1
      A8:  b           B8:  1
      A9:   Total      B9:  2
      A10:             B10: 1
      A11: c           B11: 1

NOTE: The blank cells are not associated with cells that contain values in column A.

Behavior in Microsoft Excel 97 for Windows

In Microsoft Excel 97, in the Subtotal dialog box, if the field you are selecting in the "At each change in" list contains empty cells, the behavior is the same regardless of whether you clear the Summary Below Data check box.
Example:

To see this behavior, use the following steps:

Type the following data in a new worksheet: A1: Name B1: Amount A2: a B2: 1 A3: B3: 1 A4: B4: 1 A5: b B5: 1 A6: B6: 1 A7: c B7: 1

Select cell A1 and click Subtotals on the Data menu.
In the Subtotals dialog box, click Name in the "At each change in" list, click Sum in the Use Function list, and then click Amount in the Add Subtotal To list.