Subtotal One Column and Count Another Column
October 05, 2022 - by Bill Jelen
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.
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.
-
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.
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.
This article is an excerpt from Power Excel With MrExcel
