MrExcel.com - Your One Stop for Excel Tips & Solutions

Count Records That Match a Criterion

March 25, 2022 - by

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.

You have a database with a Gender column starting in B5. You want to count how many M and F records there are.
Figure 402. Count males and females.

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”).

The formula to count M records is =COUNTIF(B5:B60,"M")
Figure 403. 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:

=COUNTIF(D5:D60,32)

=COUNTIF(D5:D60,“32”)

You can also establish a criterion to look for items that are below or above a certain number:

=COUNTIF(D5:D60,“<21”)

A criterion can include a wildcard character. To find any text that contains XYZ, you use the following formula:

=COUNTIF(A2:A999,”*XYZ*”)

This article is an excerpt from Power Excel With MrExcel

Title photo by Bernard Hermant on Unsplash