XL: Subtotals for Every Change in Text Group

ID: Q112373

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.

REFERENCES

"Online Help," version 5.0
"Function Reference," version 4.0, pages 225, 299, 423
"Function Reference," version 3.0, pages 125, 163, 231

Additional query words: 4.00a

Keywords : xlformula
Version : WINDOWS: 3.0,4.0,4.0a,5.0; MACINTOSH: 3.0,4.0
Platform : MACINTOSH WINDOWS
Issue type : kbhowto