Excel tables with their functions come in handy to count values or cells with data, horizontally and vertically. COUNTIF is the common function for counting cells with one condition. So let’s look into this step-by-step tutorial to understand how you can use this function for your calculations.
Meaning of COUNTIF Excel
COUNTIF in Excel is a statistical function that counts cells with the data that users indicate. Basically, this is a combination of COUNTA and IF functions – it takes into account one condition.
- You can find items recorded even for months under a specific name.
- You may count the number of cells that hold a common letter or sign.
- You can use the Excel COUNTIF not blank to count blank cells.
- You specify numerosity more or less than a mentioned value.
Note: Unlike the SUMIF function which totals values, COUNTIF only counts values.
What is Excel COUNTIF syntax?
The COUNTIF formula in Excel consists of range (which goes first) and criteria (indicates the corresponding position).
=COUNTIF (range, criterion) rangeis the mandatory argument, which includes the range of cells needed to find.criterionincludes expression, words, figures, letters, or other conditions that you need to discover.
How COUNTIF works in Excel?
The function calculates the number of cells with indicated context, i.e., with the data that the user needs to count. For instance, you have a chart with diversified furniture bought over time. You may count what furniture (sofa, bed, wardrobe, chair, table, etc.) people buy. The Excel COUNTIF function will compute the cells in which the mentioned furniture is located. You write down the formula according to the condition you ought to detect. For example, let’s count the number of cells containing the word ‘chair‘:
=COUNTIF(A1:A21,"Chair")
How to use COUNTIF in Excel?
The function works with numbers, text strings, and dates. You can only write a cell reference or an entire condition. Sometimes, wildcards and symbols assist in determining more accurate values.
Does COUNTIF function in Excel work for several criteria?
The COUNTIF function counts values based on only one condition. Thus, you won’t find the number of chairs or nightstands purchased only in January or on February 3. You have to use COUNTIFS for such cases.
However, Excel beginners may use a combination of COUNTIF + COUNTIF. Such a COUNTIF Excel multiple criteria function makes the outcome more complete. Let’s determine the number of purchased chairs and sofas. Write the following:
=COUNTIF(A2:A21,"Chair")+COUNTIF(A2:A21,"Sofa").
Samples of advanced COUNTIFS function in Excel
COUNTIF and COUNTIFS are various functions. They differ in the computation of cells with one criterion or more criteria. The first identifies cells under one condition, and the second takes into account several specific criteria. Let’s count the number of chairs bought by Jeremy. We need to use COUNTIFS for such appointments. Write it as follows:
=COUNTIFS(A2:A13,"Chair",B2:B13,"Jeremy")
Get data from your sources to Excel
People usually export data from their apps to Excel manually by downloading CSV or XLSX files. However, what if there is a solution to connect any of your cloud apps to Excel and automate data exports? With Coupler.io, you only need to make a few clicks to complete this.
- Select the source application in the form below and click Proceed. You’ll be offered to create a Coupler.io account for free.
- Then connect your source application and specify which data to export.
- Preview and organize your dataset and then specify the workbook and the worksheet to load your data.
- The last step is to schedule a data refresh to access the most recent data in your Excel file.
Coupler.io supports 60+ data sources including marketing apps, accounting software, databases, and so on. You can also use Excel as a source app, i.e. export data from one Excel file to another.
Practical skills based on a COUNTIF Excel example
Examine a few samples of use in more detail. Take, for instance, the furniture in the store. Imagine the owner created a Microsoft Excel table regarding sales. They write down the furniture in one column there. The other column contains the dates when the goods were sold. Finally, the third column contains the quantity of each product sold. So, let’s define some samples with several formulas, including dates, words, and figures.
Excel COUNTIF: Cells are equal or not equal
Imagine that people have bought every subject several times. Let’s take, for example, 7 times. In our formula, we specify the range where to look and the criteria – figure 7.
=COUNTIF(B1:B20,7)
Let’s still find the purchased furniture that isn’t equal to 5.
=COUNTIF(B1:B20,"<>5")
Excel COUNTIF: cells contain words
Let’s try to work with words using the same formula. Imagine that we need to know how many tables people bought. Let’s write the following formula.
=COUNTIF(A2:A21,"Table")
As a result, people bought 2 tables. You can replace the word with a cell reference
=COUNTIF(A2:A21,E2)
COUNTIF use in Excel – meaning of wildcards for partial matching
We can use COUNTIF not only with text strings but also with special wildcards (* and ?) to generalize the condition.
?(question mark) – replaces a single character.*(asterisk) – replaces a sequence of characters (text values or numeric values).
Also, you can write ~ (tilde) to find symbols * or ?.
Let’s look at the table with a simple formula in the column B:
=COUNTIF(A1:A7,"Table")
The question mark symbol ? adds one additional character. In our dataset, there are the words ‘Table‘ and ‘Tables‘ with the last letter ‘s‘. So, Table? in the formula will look for the latter option:
=COUNTIF(A1:A7,"Table?")
The asterisk symbol * adds a sequence of characters or even spaces. For instance, the following formula counts all words that start with Tab including ‘Table’ and ‘Tables’:
=COUNTIF(A1:A7,"Tab*")
Excel COUNTIF: count numbers that are more than or less than
Let’s find out some values using logical operators (>, <, <>, =). We write it next to the criterion in quotation marks. Thanks to them, we can count numbers that are more or less, equal, or not equal to some value, for example, empty cells.
Count the amount of furniture that people bought more than 3 times:
=COUNTIF(B1:B20,">3")
Change the logical operator to count the amount of furniture that people bought less than 3 times:
=COUNTIF(B1:B20,"<3")
Excel COUNTIF: count cells including date
Another feature is working with dates. So, let’s imagine that the biggest purchases fall on the date of January 1. We need to know the cells’ numerosity with this data.
=COUNTIF(B2:B21,"01/01/2021")
What common problems should you avoid when using COUNTIF in Excel?
Often, people face issues using the function. We seem to be doing everything right, but the Excel function COUNTIF shows the error. Look at a few situations why it occurs. For instance, you wrote the following Excel formula:
=COUNTIF(A2:A13,Chair)
The function has shown zero results although this is not correct. So taking a closer look, the word ‘Chair‘ should be in quotation marks.
=COUNTIF(A2:A13,"Chair")
In another example of incorrect syntax, the formula returned #NAME?. In this case, there is a space between the range (A1:A7) and the criterion, which also has not quotation marks (Table).
The correct formula should look like this
=COUNTIF(A1:A7,"Table")
Best practices in using the COUNTIF function
It is easy to make mistakes both out of ignorance and inattention. However, by applying the following tips you can avoid the common mistakes.
- Write wildcard characters (
?and*) to generalize the condition. - The condition can only have less than 255 characters. You will see an error exceeding this limit. To exceed the limit, you can use CONCATENATE or ampersand (&).
- Pay attention to the spelling of words and letters. The function does not differentiate case strings and doesn’t work with misspellings.
- The function returns the same value if the words are written in uppercase or lowercase letters. Conditions are not case-sensitive and the function doesn’t take into account case strings. You should rename the word if it has a different meaning.
Adherence to all principles and rules and writing correct syntax will facilitate calculations without problems. The main point is to write the formula and condition correctly.
Automate data export to Excel with Coupler.io
Get started for free
