Data Associated with Blank Cells May Be Excluded from Subtotals

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

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

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

   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

   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:

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

2. Select cell A1 and click Subtotals on the Data menu.

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

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

5. Select cell A1 and click Subtotals on the Data menu.

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

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:

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

2. Select cell A1 and click Subtotals on the Data menu.

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

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

5. Select cell A1 and click Subtotals on the Data menu.

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