Microsoft KB Archive/164499

= Data Associated with Blank Cells May Be Excluded from Subtotals =

Article ID: 164499

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q164499



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.

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.



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.</li>  Click OK.

The data appears as follows:

<pre class="fixed_text">     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. </li> Select cell A1 and click Subtotals on the Data menu.</li>  In the Subtotal dialog box, click to clear the Summary Below Data check box, and then click OK.

The data appears as follows:

<pre class="fixed_text">     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 </li></ol>

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:

<ol>  Type the following data in a new worksheet: <pre class="fixed_text">     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 </li> Select cell A1 and click Subtotals on the Data menu.</li> 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.</li>  Click OK.

The data appears as follows:

<pre class="fixed_text">     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 </li> Select cell A1 and click Subtotals on the Data menu.</li>  In the Subtotal dialog box, click to clear the Summary Below Data check box, and then click OK.

The data appears as follows:

<pre class="fixed_text">     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 </li></ol>

<div class="references_section">