In Microsoft Excel, COUNTIF is among the most commonly used solutions. It counts all cells in an array that matches a solitary condition or numerous conditions, and it’s equally valuable in counting cells with numbers and also message in them.
What Is the COUNTIF feature?
COUNTIF allows individuals to count the number of cells that meet particular requirements, such as the number of times a component of a word or specific words shows up on a checklist. In the actual formula, you’ll tell Excel where it needs to look and also what it requires to seek. It counts cells in an array that fulfills solitary or several problems, as we’ll demonstrate below.
Exactly how to Use the COUNTIF Formula in Microsoft Excel
For this tutorial, we will certainly use easy two-column inventory graph logging institution supplies and also their amounts.
In an empty cell, type =COUNTIF followed by an open brace. The first debate “variety” requests the range of cells you wish to check. The 2nd debate “requirements” requests for just what you desire Excel to count. This is typically a text string. So, in double-quotes, include the string you want to find. Be sure to include the closing quotemark as well as the closing bracket.
So in our example, we want to count the number of times “Pens” shows up in our inventory, which includes the array G9: G15. We’ll utilize the following formula.
You can likewise count the number of times a certain number shows up by placing the number in the requirements argument without quotes. Or you can utilize drivers with numbers within quotes to figure out results, like “
How to Count the Number of Multiple Values
To count the variety of numerous worths (e.g. the total of pens as well as erasers in our supply graph), you might utilize the following formula.
This counts the variety of erasers and pens. Keep in mind, this formula uses COUNTIF twice given that there are numerous requirements being used, with one criterion per expression.
Limitations of the COUNTIF Formula
If your COUNTIF formula uses standards matched to a string longer than 255 characters, it will return an error. To fix this, make use of the CONCATENATE feature to match strings longer than 255 characters. You can avoid keying out the full function by simply using an ampersand (&), as demonstrated below.
One actions of COUNTIF features to be familiar with is that it ignores top and reduced situation strings. Criteria that consist of a lower case string (e.g. “erasers”) and also a top instance string (e.g. “ERASERS”) will match the very same cells as well as return the very same worth.
Another habits of COUNTIF features involves using wildcard characters. Using an asterisk in COUNTIF criteria will certainly match any series of characters. As an example, =COUNTIF(A2: A5, “* eraser *”) will certainly count all cells in an array that contain words “eraser.”
When you’re counting worths in a variety, you might have an interest in highlighting the top- or bottom-ranked worths.