Comprehensive Tutorial on Implementing the COUNTIF Feature in MS Excel for Data Analysis

Quick Links

• What Is the COUNTIF function?
• How to Use the COUNTIF Formula in Microsoft Excel
• How to Count the Number of Multiple Values
• Limitations of the COUNTIF Formula

In Microsoft Excel,

    `COUNTIF`

is one of the most widely used formulas. It counts all cells in a range that matches a single condition or multiple conditions, and it’s equally useful in counting cells with numbers and text in them.

What Is the COUNTIF function?

    `COUNTIF`

allows users to count the number of cells that meet certain criteria, such as the number of times a part of a word or specific words appears on a list. In the actual formula, you’ll tell Excel where it needs to look and what it needs to look for. It counts cells in a range that meets single or multiple conditions, as we’ll demonstrate below.

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.

Related: How to Count Colored Cells in Microsoft Excel

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

Related: How to Use the FREQUENCY Function in Excel

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

When you’re counting values in a range, you may be interested in highlighting the top- or bottom-ranked values .

| | Mastering Excel Functions | | |
| —————————- | —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————— | |
| Functions | AVERAGE · CONCATENATE · COUNT · COUNTIF · DATEDIF · FILTER · FREQUENCY · FV · HYPERLINK · IF · IFS · IMAGE · INDEX · IS · LEN · MATCH ·MEDIAN · RAND · ROUND · RRI · SORT · SQRT · SUBSTITUTE · SUBTOTAL · SUM · SUMIF · TODAY · TRIM · TRUNC · VLOOKUP · WEEKDAY · XLOOKUP · YEAR | |
| Types | Basic · Budgeting · Data Entry · Logical · Text · Time and Date | |
| Explained | Copying Formulas · Evaluating Formulas · Finding Functions · Fixing Formula Errors · Functions vs Formulas · Comparing Lookup Functions · Locking Formulas · Structuring Formulas · Translating Formulas | |

Also read: