Microsoft KB Archive/112373

{|
 * width="100%"|

XL: Subtotals for Every Change in Text Group

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0

-

SUMMARY
In Microsoft Excel, you can use three columns of data and a formula to evaluate the change of a sorted text entry in one column, and then subtotal a second column of values with the subtotal displaying the results in a third column.

MORE INFORMATION
The following example generates subtotals in a third column of the values in a second column for every change in sorted text of a first column.

Example
To see an example, follow these steps:

  In a new worksheet, enter the following data:

     A1  TEXT     B1  VALUES   C1  SUBTOTALS A2          B2           C2      A3   Bob     B3    1      C3      A4   Bob     B4    2      C4      A5   Sam     B5    3      C5      A6   Sam     B6    4      C6      A7   Sam     B7    5      C7      A8  Frank    B8    6      C8      A9  Frank    B9    7      C9  In cell C3, enter the following formula:

=IF(A3=A4,,SUM($B$3:OFFSET(C3,0,-1))-SUM($C$3:OFFSET(C3,-1,0))) Use the fill handle to fill this formula down column C to the last row of adjacent values in column B.

NOTE: There should be at least one blank row above the data range and the text entries in the first column must be sorted alphabetically.