COUNTIF is a popular formula in Microsoft Excel. It counts all cells in a range that fits a single or many conditions, and it works equally well with cells that include numbers and text.
We will create a basic two-column inventory chart to track school supplies and their amounts in this lesson.
Type =COUNTIF followed by an open bracket in an empty cell. The first option, “range,” specifies the range of cells to be checked. The second option, “criteria,” specifies what you want Excel to consider. This is commonly a text string. So, enclose the string you wish to locate in double quotes. Remember to include the closing quotemark and bracket.
So, in our example, we want to count the number of times “Pens” occurs in our inventory, which encompasses the range G9:G15. We’ll use the formula below.
=COUNTIF(G9:G15,"Pens")
You may also count the number of times a given number appears by putting it in the criterion argument without quotes. You may also use operators with numbers within quotes to get results, such as “100” to get a count of all numbers less than 100.
You may use the following formula to count the number of multiple values (for example, the number of pens and erasers in our inventory chart).
=COUNTIF(G9:G15, "Pens")+COUNTIF(G9:G15, "Erasers")
This tallies the number of erasers and pens used. Because there are several criteria, this formula utilizes COUNTIF twice, with one criterion per expression.
If your COUNTIF formula matches the criteria to a string that is larger than 255 characters, an error will be returned. To correct this, use the CONCATENATE function to match strings longer than 255 characters. To avoid typing out the entire function, simply use an ampersand (&), as seen below.
=COUNTIF(A2:A5,"long string"&"another long string")
One thing to keep in mind with COUNTIF functions is that it ignores upper and lower case strings. Criteria that include both a lower case and an upper case string (for example, “erasers”) will match the same cells and yield the same response.
Another feature of COUNTIF functions is the usage of wildcard characters. In COUNTIF criteria, an asterisk will match any sequence of characters. =COUNTIF(A2:A5, “*eraser*”), for example, will count all cells in a range that include the word “eraser.”
ManilaShaker is a tech media producing insightful and helpful content for our local and growing international audience. Our goal is to create a premier Philippine digital consumer electronics resource that provides the most objective reviews and comparisons globally.
