Let’s take a look. The COUNTIF function counts cells that satisfy a single condition that you supply. It takes two arguments: range and criteria. For example, if I want to count the cells in this range that contain the number “15,” I enter B7:B12 for the range, and “15” for the criteria. Excel then returns “1,” since only one cell contains “15”. If I temporarily enter another “15” that result will change. You can add logical operators to the criteria. To count cells with a value greater than “15,” I enter the criteria of “>15” in double quotes. When the criteria contains a logical operator, you’ll need to enclose it in double quotes. You can use COUNTIF with both text and numbers. To count the number of cells that contain “apple,” the criteria is simply “apple” in double quotes. Note that COUNTIF is not case-sensitive. You can use empty double quotes to count blanks cells. COUNTIF also supports wildcards. “P” plus an asterisk will return “3,” since three entries begin with a “p”. You can use COUNTIF to count dates that meet one condition as well. To count dates greater than January 1, 2013, enter the “greater than” operator and the full date in double quotes. Follow the same process to count dates that are less than January 1, 2012. Because dates appear in a different format in many parts of the world, a safer option is to use the DATE function in your criteria. This will ensure that Excel always recognizes the date correctly. To use the DATE function, you’ll need to concatenate the operator and the date function together in the criteria. Now both F15 and F16 return the same result. You can also move the date out onto the worksheet where it can be easily changed. Again, you’ll need to concatenate the operator with the cell reference.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
