Learn how to use the COUNTIF function to find out how many items in an Excel list satisfy a certain condition.
Problem: You have a large dataset, as shown in Fig. 562. You want to count the number of records that meet a certain criteria. What function should you use?
Note: Click any image below to see a larger view of that figure.
Strategy: Use the COUNTIF function. This function requires two arguments. The first is a range of cells that you want to test. The second is a test. To count the records where the gender is “M”, use =COUNTIF(B2: B57, “M”), as shown in Fig. 563.
Note that the second argument of “M” indicates tells Excel to count records that are equal to M. Since this function is not case sensitive, the function will count cells with M or m.
If you want to count the records where the age is a specific number, as shown in Fig. 564, then you can write the formula either with or without quotes around the number:
=COUNTIF(C2:C999,32)
=COUNTIF(C2:C999,“32”)
The criteria can look for items that are below or above a certain number:
=COUNTIF(C2:C999,“21”)
Summary: To count how many cells contain certain criteria, use the COUNTIF function by entering the two criteria it needs, which cells to count, and what to count.
Functions Discussed: =COUNTIF()
Images
References and Additional Resources
If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel . This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel. If you don’t find what you’re looking for, leave a note in the Comments section below and let us know.
Other Resources:
Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/
Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com .
