XL: Data Associated with Blank Cells May Be Excluded from Subtotals

ID: Q164499

The information in this article applies to:

Microsoft Excel for Windows, version 5.0
Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel 5.0 or 7.0, when create a list of data that contains empty cells, and you use the Subtotals command on the Data menu, the summary values for the data may be incorrect.

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.

In the "At each change in" box in the Subtotal dialog box, you specify a column that contains blank cells.

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

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel versions 5.0 and 7.0 for Windows. This problem was corrected in Microsoft Excel 97 for Windows.

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.

Click OK.

The data appears as follows:


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

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:              B5:  1
      A6:              B6:  1
      A7:  b Total     B7:  1
      A8:  b           B8:  1
      A9:              B9:  1
      A10: c Total     B10: 1
      A11: c           B11: 1

REFERENCES

For more information about subtotals, click the Index tab in Microsoft Excel Help, type the following text

Subtotals, data summary

and then double-click the selected text to go to the "Summarize data by using subtotals and outlines" topic.

Additional query words: sub total

Keywords : kbtool kbdta xllist
Version : WINDOWS:5.0,7.0
Platform : WINDOWS
Issue type : kbbug