» Counting the Values that Match Criteria in a Filtered List
CATEGORY - Counting
VERSION - All Microsoft Excel Versions
Column B shows the answers to the question numbers displayed in column A.
An auto-filter is set for both columns.
We want to create a formula that will count the number of times each possible answer (listed in cells A26:A29) occurs for each of the filtered question numbers.
Solution:
Use the SUMPRODUCT, SUBTOTAL, OFFSET, ROW, and MIN functions as shown in the following formula:
=SUMPRODUCT(($B$2:$B$22=A26)*(SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$22)-MIN(ROW($B$2:$B$22)),,))))
Enter the above formula next to the 4 possible answers (cells B26:B29).
Book Store:
Recommended Books:
- Essentials of Investments with Standard & Poor's Educational Version of Market Insight + PowerWeb + Stock Trak Coupon
- Business Analysis with Microsoft Excel (2nd Edition)
- Word 2002: The Complete Reference
- Microsoft Excel 2002 Formulas (With CD-ROM)
- Rich Dad, Poor Dad: What the Rich Teach Their Kids About Money--That the Poor and Middle Class Do Not!
- Advanced modelling in finance using Excel and VBA
No comments have been submitted.
