The COUNTIF function is one of the most commonly used functions in the powerful Excel processor from Microsoft. What is it for? This is clear from its name. It will help any user who wonders, “How to count cells in Excel by a given criterion?”, Get the right numbers.
Function Arguments
This function takes two required arguments. The first of them is the range in which the number of cells satisfying the condition will be counted.
The second argument is a condition that can be specified in the form of a number or a word, as well as in the form of a condition.
We’ll prepare a range of data and based on it we will look at how the examples specific to the COUNTIF function in Excel look like. Suppose we have a list of fruits and vegetables stored in a warehouse with a list of their purchase price, sale premium and date of purchase. Work begins with tables in Excel - we transfer the following data to the worksheet:
| A | IN | FROM | D |
1 | Fruits vegetables | Purchase price (kg) | Premium (%) | Date of purchase |
2 | Gold apples | 74.5 | 28 | 05/01/2017 |
3 | Fuji Apples | 69.8 | fifty | 05/16/2017 |
4 | Bananas | 41 | 75 | 05/04/2017 |
5 | Bananas mini | 74.5 | 42 | 05/14/2017 |
6 | Gloucester apples | 54,2 | 34 | 05/15/2017 |
7 | Chinese cucumber | 64 | 17 | 05/07/2017 |
8 | Pollinated cucumber | 110.6 | 28 | 05/16/2017 |
9 | Unpolished cucumber | 132.9 | 28 | 05/01/2017 |
Important addition
The second argument to the function is a condition. If we want to specify the number or address of the cell as an argument, then we can write directly. If you want to build a complex condition with comparison signs, search for text or a specific date, then the argument must be enclosed in quotation marks. Below we will also consider how using the sign "ampersand" you can combine various functions in a condition.
Simple conditions for a function
The dataset introduced in the previous section will help you figure out the COUNTIF function in Excel. The examples discussed in this section will be quite simple, but they are fundamental, and you need to consider them before proceeding to the more complex ones.
In tabular form, we summarize the formulas, the results of their implementation, as well as write a short explanatory comment. All answers are based on the tabular data entered above and are executed on the date May 16, 2017.
Example | Answer | A comment |
= COUNTIF (B2: B9; 74.5) | 2 | It reveals how many goods in stock were purchased at a price of 74.5 |
= COUNTIF (A2: A9; A2) | 1 | If we stretch this formula to the end of the table, we will see if we have duplicates by the name of the goods (if 1, then there are no duplicates) |
= COUNTIF (D2: D9; TODAY ()) | 2 | The number of cells with goods that arrived at the warehouse today (the system date of the computer is used) |
= COUNTIF (C2: C9; "") | 0 | Counts the number of unfilled cells in the range. Attention! If a space is set in a cell, then it is not considered empty |
= COUNTIF (B2: B9; "<55") | 2 | The number of cells with a value less than 55. In a similar way, a comparison greater than ">" and not equal to "<>" |
= COUNTIF (B2: B9; "<55") + COUNTIF (B2: B9; "> 100") | 4 | The total number of cells found by a combination of two conditions |
Wildcards
So-called wildcards are widely used with the COUNTIF function in Excel. Examples of wildcards:
Sign | Description |
* | Text (any number of characters) |
? | Text (the number of characters corresponds to the number of question marks). Only works with text data! If there is no letter in the cell, it will always show 0 |
Full-fledged work with tables in Excel and analysis of the data stored in them is almost impossible without the use of wildcards.
Conditions for a wildcard function. Function combination
Combining with other functions to create the desired condition occurs by combining the logical condition ("more", "less", "not equal") and the desired function with the sign "ampersand" (&).
In tabular form, consider more complex examples of using the "COUNTIF" function.
Example | Answer | A comment |
= COUNTIF (A2: A9; "apples") | 0 | The number of cells containing only the word "Apples" (case insensitive) and all |
= COUNTIF (A2: A9; "apples *") | 3 | Number of cells starting with the word "Apples" and any ending |
= COUNTIF (A2: A9; "* d *") | 3 | The number of cells containing the letter "y" in any part of the text. Often used to search for and further replace “y” with “and” and “e” with “e” |
= COUNTIF (A2: A9; "??????") | 1 | Items with a length of only 6 characters |
= COUNTIF (D2: D9; "<" & (TODAY () - 10)) | 3 | Number of cells with a purchase date older than ten days ago |
= COUNTIF (C2: C9; ">" & AVERAGE (C2: C9)) | 3 | The number of cells with a sales markup value greater than the average |
This concludes our discussion of the COUNTIF function in Excel.
The examples contained in the article will help you understand the basic principles of construction and the combination of conditions used as an argument to a function, and correctly apply them in practice.