How to use the COUNTIF formula in Microsoft Excel

How to use the COUNTIF formula in Microsoft Excel

In Microsoft Excel, COUNTIFit is one of the most used formulas. Counts all cells in a range that matches one or more conditions and is equally useful for counting cells with numbers and text.

What is the COUNTIF function?

COUNTIFallows users to count the number of cells that meet certain criteria, such as the number of times a part of a specific word or words are displayed in a list. In the actual formula, you will tell Excel where to look for and what to look for. Counts cells in a range that satisfies single or multiple conditions, as we will demonstrate below.

How to use the COUNTIF formula in Microsoft Excel

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

In a blank cell, type =COUNTIF followed by an opening parenthesis. The first argument “range” requires the range of cells you want to check. The second “criteria” argument asks what exactly you want Excel to count. It is usually a text string. Then, in quotes, add the string you want to find. Make sure you add the closing quotation mark and closing parenthesis.

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

=CONTA.SE(G9:G15;"Penne")

countif formula example

You can also count the number of times a specific number is displayed by entering the number in the criteria argument without quotes. Or you can use operators with numbers inside quotes to determine the results, for example "<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 table), you can use the following formula.

=COUNTIF(G9:G15, "Penne")+COUNTIF(G9:G15, "Gomme")

countif plus example formula

This counts the number of erasers and pens. Note, this formula uses COUNTIF twice as multiple criteria are 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 work around this problem, use the CONCATENATE function to match strings longer than 255 characters. You can avoid typing the entire function by simply using an ampersand (&), as shown below.

=COUNTIF(A2:A5,"stringa lunga"&"un'altra stringa lunga")

One behavior of the COUNTIF functions to be aware of is that it ignores uppercase and lowercase strings. Criteria that include a lowercase string (eg “tires”) and an uppercase string (eg “TIRES”) will match the same cells and return the same value.

Another behavior of the 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 contains the word “rubber”.

Similar Posts

Leave a Reply

Your email address will not be published.