Our Excel article shows you which functions to use when you want to count data that you’ve already filtered. To view the graphics to the table references in this article, please view the pdf document located here.
If you’ve ever tried to count the number of rows in your data table, you might have felt frustrated when you filter the data and realize your row count remains the same. Even when you filter the data, your count might still include the entire data table — especially if you use the COUNT function. Instead, we’ll show you how the SUBTOTAL function can give you the accurate total you need. Your SUBTOTAL formula will update with your filtered data — whether you’re working with text or numeric data.
Cast out the COUNT function
The COUNT function comes in handy when you’re dealing with numeric data, but it doesn’t play well with filtered data. First, the COUNT function only recognizes numeric data. If you include text data in the COUNT function’s range, the formula won’t recognize the text data. If your data range only includes numeric data, the COUNT function includes every row — even rows hidden when you filter the data.
Even the COUNTA function, which recognizes both text and numeric data, still includes hidden filtered rows in its final tally.
To illustrate these functions’ failings, take a look at our comparison table in Figure A. Our data includes 12 months of expense and revenue data. We’ve filtered the data to show the five months with the highest expenses. So, logically, our row count should equal five. But even when we include different ranges — the entire data table, a column of text data only, and a column of numeric data only — none of the COUNT or COUNTA functions give us the correct result.
Adapt for Excel 2007
The SUBTOTAL function works the same way in Excel 2007 as it does in earlier versions. The only difference is that Excel 2007 allows up to 254 ref arguments (ranges to include in the SUBTOTAL formula) whereas earlier versions allow only 29 arguments.
Important:
Note that when you include the entire data table in your COUNT or COUNTA function’s range, the formula counts every cell. If you want to count the number of rows in your filtered data table, you should only include one column of the data range in your formula.
Get the right results with the SUBTOTAL function
Don’t despair! The SUBTOTAL function can give you the accurate row count you need for a filtered data table. The SUBTOTAL function follows this syntax: =SUBTOTAL(function_num, ref1, ref2, . . .) The ref1 and ref2 arguments represent any ranges or references you want to include in the subtotal. You can add up to 29 ref arguments, and they should include columns — not rows. The SUBTOTAL function is designed for columns, according to Microsoft.
The function_num argument represents the function you want Excel to use when it subtotals the data in your ref ranges. We’ve listed all of the available functions and their function_num equivalents in Table A.
Learn how hidden data factors into the equation
Table A includes function_num values that both include and exclude hidden values. You might assume that filtered data includes hidden data — after all, the AutoFilter does its job by hiding rows that don’t fit your chosen criteria. But Excel doesn’t consider filtered data “hidden” in this case. When it comes to the SUBTOTAL function, hidden data refers only to rows that you hide by choosing Format Row Hide from the menu bar (or right-clicking on a row number and choosing Hide from the shortcut menu).
So in most cases, it won’t matter which set of function_num values you use for filtered data. But if your data table does include rows you’ve hidden manually, pay attention to whether you want to include those hidden rows in your SUBTOTAL results.
Choose the right function_num value
For our purposes, we’ll need to use either the COUNT or the COUNTA function to count the number of rows in our filtered data. We’ve updated our comparison table to include SUBTOTAL formulas that use COUNT and SUBTOTAL formulas that use COUNTA.
When you use a range that includes column B, which contains numeric data, you can use COUNT with SUBTOTAL for an accurate row count. You can also use COUNTA in this case because COUNTA recognizes both numeric and text data. But when you use a range that includes column A, which contains text data, the COUNTA function_num argument gives you an accurate count. For text data, you can’t use the COUNT function.
For this reason, if you’re counting data that doesn’t contain consistent formatting (such as dates), the COUNTA function_num is your safest bet.
Watch your data flex
When you change the filter on your data range, watch your SUBTOTAL formula’s results update to match the new row count. While using the COUNT or COUNTA functions alone give you the wrong results, the SUBTOTAL function works.
To view the graphics to the table references in this article, please view the pdf document located here.
Business skills for the new world of work
In business today, productivity is key to your success. Whether that means setting up projects for success, forecasting and analyzing trends, or managing critical business information, it is vital that you have the skills to work at peak performance. You already know how to use Microsoft® Office System applications. New Horizons offers Microsoft Business Skills Series Courses to teach you how to use those applications to more efficiently manage, work with, and prioritize information to make better decisions.
Go to www.NewHorizons.com/ for information on courses that cover topics such as:
- 4002 Forecasting and Trend Analysis Using Microsoft Office Excel 2003
- 4004 Managing Critical Business Information Using Microsoft Office Access 2003
- 4008 Building Better Microsoft Office Word 2003 Documents In Less Time
Related Course
Posts
