You can learn how to use Excel Countif formula with this article. Excel COUNTIF is a widely used formula. It counts all cells in a range that satisfy a single condition or several conditions, and it’s also useful for counting cells with numbers or text in them. What Is the Excel COUNTIF formula?
The COUNTIF function in Excel allows you 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. You’ll tell Excel where it needs to look and what it should look for in the actual formula. It counts cells in a range that satisfies single or multiple criteria, as we’ll show in this article.
How to use the Excel COUNTIF formula?
For this guide, we will use simple inventory chart logging fruits. In an empty cell, type =COUNTIF and an open bracket. The first argument “range” specifies the range of cells you want Excel to count. The second argument “criteria” is what you want to be counted in that range. This is typically a text string. So, put the thing you’re looking for in double-quotes. Make sure to include the closing quote mark and bracket at the end.
So in our example, we want to count the number of times “Apple” appears in our inventory, which has the range of B4:B13. For that, we will use the following formula:
=COUNTIF(B4:B13;”Apple”)
You may also count the number of times a specific number appears by including it in the criteria argument without quotes. Alternatively, you can use operators with numbers within quotations to get answers, such as “<100” to obtain a total count of all values less than 100.
How to count the number of multiple variables?
For counting the number of many values (for example, the total of apples and peaches in our inventory chart), you may use the following formula:
=COUNTIF(B4:B13;”Apple”)+COUNTIF(B4:B13;”Peach”)
This outputs the number of apples and peaches. Because there are two distinct criteria in this calculation, therefore COUNTIF is employed twice, once for each expression.
Limitations of the Excel COUNTIF formula
If your Excel COUNTIF formula has criteria that are greater than or equal to 255 characters, it will return an error. To overcome this problem, utilize the CONCATENATE function to compare strings that are lengthier than 255 characters. You may avoid typing out the complete function by utilizing an ampersand (&), as shown below.
=COUNTIF(A2:A5,”long string”&”another long string”)
One particularity of COUNTIF is that it ignores upper and lower case strings. Lower-case string criteria (e.g., “apple”) and uppercase string criteria (e.g., “APPLE”) will match the same cells and return the same value.
COUNTIF functions can also be used to count cells that contain a certain string of text, as demonstrated by the wildcard characters. Using an asterisk in COUNTIF criteria will match any sequence of characters. For example, =COUNTIF(A2:A5,”*apple*”) will count all cells in a range that include the word “apple.”
We hope that you found this article useful and now can utilize the Excel COUNTIF formula in your work.
