- Excel Book Excerpt
Excel Count Records That Match a Criterion
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Count Records That Match a Criterion
Problem: I have a large data set. I want to count the number of records that meet a certain criterion.
Strategy: You use the COUNTIF function, which requires two arguments: a range of cells that you want to test and a criteria. To count the records where the gender is M, you use =COUNTIF(B5:B60,“M”).
Figure 410 COUNTIF function looks through a range, counting matches.
Note that the second argument, “M”, tells Excel to count records that are equal to M. Because this function is not case-sensitive, the function will count cells with values of M or m.
If you want to count the records where the age is a specific number, you can write the formula either with or without quotes around the number:
You can also establish a criterion to look for items that are below or above a certain number:
A criterion can include a wildcard character. To find any text that contains XYZ, you use the following formula:
For more resources for Microsoft Excel: