Use the COUNTIF Formula in Microsoft Excel

 

How to Use the COUNTIF Formula in Microsoft Excel

For this tutorial, we will use simple two-column inventory chart logging school supplies and their quantities.

In an empty cell, type =COUNTIF  followed by an open bracket. The first argument “range” asks for the range of cells you would like to check. The second argument “criteria” asks for what exactly you want Excel to count. This is usually a text string. So, in double-quotes, add the string you want to find. Be sure to add the closing quotemark and the closing bracket.

So in our example, we want to count the number of times “Pens” appears in our inventory, which includes the range G9:G15 . We’ll use the following formula.

=COUNTIF(G9:G15,"Pens")

You can also count the number of times a specific number appears by putting the number in the criteria argument without quotes. Or you can use operators with numbers inside of quotes to determine results, like "<100" to get a count of all numbers less than 100.

How to Count the Number of Multiple Values

To count the number of multiple values (e.g. the total of pens and erasers in our inventory chart), you may use the following formula.

=COUNTIF(G9:G15, "Pens")+COUNTIF(G9:G15, "Erasers")

This counts the number of erasers and pens. Note, this formula uses COUNTIF twice since there are multiple criteria being used, with one criterion per expression.

Limitations of the COUNTIF Formula

If your COUNTIF formula uses criteria matched to a string longer than 255 characters, it will return an error. To fix this, use the CONCATENATE function to match strings longer than 255 characters. You can avoid typing out the full function by simply using an ampersand (&), as demonstrated below.

=COUNTIF(A2:A5,"long string"&"another long string")

One behavior of COUNTIF functions to be aware of is that it disregards upper and lower case strings. Criteria that include a lower case string (e.g. “erasers”) and an upper case string (e.g. “ERASERS”) will match the same cells and return the same value.

Another behavior of COUNTIF functions involves the use of wildcard characters. Using an asterisk in COUNTIF criteria will match any sequence of characters. For example, =COUNTIF(A2:A5, "*eraser*") will count all cells in a range that contain the word “eraser.”