XL: Subtotals for Every Change in Text Group

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

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

2. In cell C3, enter the following formula:

         =IF(A3=A4,,SUM($B$3:OFFSET(C3,0,-1))-SUM($C$3:OFFSET(C3,-1,0)))
   

3. Use the fill handle to fill this formula down column C to the last row of adjacent values in column B.

REFERENCES

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