How to use the COUNTIF Formula in Microsoft Excel

At Microsoft Excel, COUNTIF is one of the most commonly used formulas. It counts all cells in a range that match a single condition or multiple conditions, and is also useful for 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, e.g. B. How often a part of a word or certain words appear in a list. In the actual formula, you will say it Excel where to look and what to look for. It counts cells in a range that meets one or more conditions, as we’ll show below.

How to use the COUNTIF Formula in Microsoft Excel

For this tutorial, we’ll use a simple two-column inventory chart that tracks school supplies and their quantities.

Enter in a blank cell =COUNTIF followed by an open bracket. The first argument “range” asks for the range of cells you want to check. The second argument “criteria” asks what exactly you want Excel counting. This is usually a text string. So, add the string you want to search in double quotes. Be sure to add the closing quotation mark and parenthesis.

So in ours example, we want to count how often “Pens” appear in our inventory, which includes the area G9:G15 . We use the following formula.


You can also count the number of times a given number occurs by including the number without quotation marks in the criteria argument. Or you can use quoted operators to determine results, such as: "<100" to count all numbers less than 100.

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 sum of the pens and erasers in our inventory table) you can use the following formula.

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

countif multiple example formula

This counts the number of erasers and pens. Note that this formula uses COUNTIF twice because it uses multiple criteria, with one criterion per expression.

Limitations of the COUNTIF formula

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

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

How to use the FREQUENCY function in Excel

One thing to note about COUNTIF function behavior is that it ignores case-sensitive strings. Criteria that contain a lowercase string (such as “eraser”) and an uppercase string (such as “ERASERS”) match the same cells and return the same value.

Another behavior of COUNTIF functions involves the use of wildcard characters. Use of an asterisk in COUNTIF criteria matches any string. To the example, =COUNTIF(A2:A5, "*eraser*") counts all cells in a range that contain the word “eraser”.

When counting values ​​in a range, you may be interested in highlighting the highest or lowest ranked values.

How to highlight the best or worst rated values ​​in Microsoft Excel

Related Posts