» 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:
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- Marketing Plans
- Keys to Reading an Annual Report (Barron's Business Keys)
- Special Edition Using Microsoft Office XP
- Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
No comments have been submitted.
