» Counting Rows that Match Specific Criteria for Each Column
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Range A2:C7 contains the ID's, genders and ages of all the people in the room.
Cells F1:F2 contains the criteria according to which we would like to count.
As different counting criteria are required, we would vary the values stored in cells F1:F2.
What single formula will count the number of rows matching the variable criteria stored in cells F1:F2?
Solution:
Use the SUMPRODUCT function as shown in the following formula:
=SUMPRODUCT((B2:B7=F1)*(C2:C7=F2))
Book Store:
Recommended Books:
- Positioning: The Battle for Your Mind
- Good to Great: Why Some Companies Make the Leap... and Others Don't
- Mastering Excel 2000 (for beginner)
- The Ernst & Young Business Plan Guide
- 422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction
- Microsoft Windows XP Registry Guide
student loan consolidation
student loan consolidation
question
Ray
What if there were other things in the column besides just female, i.e. Female/Analyst, but I still wanted to find all the ones in with Female using the SUMPRODUCT. I tried using something like (B2:B7="*Female*"), but it doesnt like the use of the * in this situation. Any ideas?
