Excel: Subtotal One Column and Count Another Column

Problem: I want to subtotal revenue and count the number of records. The Subtotal dialog offers 11 different summary functions including two counting functions. How do I change the function for different columns?

I've tried adding the SUM to Revenue, then doing subtotals a second time to count the customer, but the subtotals end up on two different rows.

Strategy: When you add subtotals, Excel makes use of a function called =SUBTOTAL(). The first argument of the SUBTOTAL function tells Excel which summary function to use.

=SUBTOTAL(9, is the argument for sum. There are 11 functions to choose from. Microsoft arranged the arguments alphabetically.

1. SUM is ninth alphabetically. That explains the 9.

   The solution is to add automatic subtotals to the numeric columns and the text column that you want to count. Of course, the totals on the text column will be zero.

   

2. Total a text column.
   1. Select the entire text column.
   2. Use Ctrl+H to display the Find and Replace dialog.
   3. Type (9, in the Find What box.
   4. Type (3, in the Replace With box.
   5. Press Replace All.

   

3. Change the 9 argument to 3.

   This will change the SUBTOTAL function from one that sums to one that counts text entries. You will have a count in column A and a sum in column E.

   

4. Counts and sums on the same row.