Excel Subtotal One Column and Count Another Column
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
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.
Figure 675 SUM is ninth alphabetically. That explains the 9.
The solution is to add automatic subtotals to the numeric columns. Also add a subtotal to a text column. Of course, the totals on the text column will be zero.
Figure 676 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.
Figure 677 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.