In Microsoft Excel,
COUNTIFit is one of the most used formulas. Counts all cells in a range that match a single condition or multiple conditions, and is equally useful for counting cells with numbers and text in them.
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 word or specific words appear in a list. In the actual formula, you will tell Excel where to look and what to look for. Counts cells in a range that meet one or more conditions, as we will demonstrate below.
How to use COUNTIF formula in Microsoft Excel
For this tutorial, we will use a simple two-column inventory chart that records 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 that you would like to check. The second argument “criteria” asks what exactly you want Excel to count. This is usually a text string. So, enclosed in double quotes, add the string you want to find. Make sure to add the closing quotation mark 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 will use the following formula.
= CONTAR.SI (G9: G15, "Plumas")
You can also count the number of times a specific number occurs by putting the number in the criteria argument without quotes. Or you can use operators with quoted numbers to determine the results, such as
"<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 (for example, the total of pens and erasers in our inventory chart), you can use the following formula.
= CONTAR.SI (G9: G15, "Plumas") + CONTAR.SI (G9: G15, "Borradores")
This counts the number of erasers and pens. Note that this formula uses COUNTIF twice as multiple criteria are used, with one criteria per expression.
Limitations of the COUNTIF formula
If your COUNTIF formula uses criteria that match 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 writing the entire function by simply using an ampersand (&), as shown below.
= CONTAR.SI (A2: A5, "cadena larga" y "otra cadena larga")
One behavior of COUNTIF functions to watch out for is that it ignores uppercase and lowercase strings. Criteria that include a lowercase string (for example, “drafts”) and an uppercase string (for example, “DRAFT”) will match the same cells and return the same value.
Another behavior of COUNTIF functions involves the use of wildcard characters. The use of an asterisk in the COUNTIF criteria will match any sequence of characters. For instance,
=COUNTIF(A2:A5, "*eraser*")it will count all cells in a range that contain the word “draft”.
When you count values in a range, you may be interested highlight the best or worst ranked values .