Excel tables with their functions come in handy to count cells with data, horizontally and vertically. COUNTIF is the common function for counting cells with one and several conditions. So let’s look into this further to understand all the details and distinctions.
Meaning of COUNTIF Excel
COUNTIF in Excel is a statistical function that counts cells with those data that users indicate. 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 learn the empty cells quantity.
- You specify numerosity more or less than a mentioned value.
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 cells needed to find.criterionincludes expression, word, figures, letter, or other conditions that you need to discover.
How to use COUNTIF in Excel?
The function works with only one condition. Nevertheless, a person may single out many different data in turn. The computation is carried out with numbers, letters, words, phrases and dates. You can only write a cell reference or an entire condition. Sometimes, wildcards and symbols assist to determine more accurate values.
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 COUNTIF 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")
Does COUNTIF function in Excel work for several criteria?
The COUNTIF doesn’t support frequentative conditions. Thus, you won’t find the number of chairs or nightstands purchased only in January or only on February 3. You have to use COUNTIFS for such cases.
However, you may write 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")
Using COUNTIF in Excel when data is on other sources
People usually store data on certain apps, such as Harvest, Google Drive, Jira, Pipedrive, etc. When you need to calculate this data in Excel, you can import it from the app. The Coupler.io tool is designed exactly for this. It will help transfer all the necessary information to BigQuery, Google Sheets, or Excel from other sources. In addition, you can automate updates to make your work easier in the future.
Sign up to Coupler.io and complete two steps to set up your Excel integration: source and destination. If you want to schedule automatic data refresh, you’ll need to configure one more step – schedule. Here is what the integration may look like:
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 an 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 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
We can use COUNTIF not only with words but also with special wildcards (* and ?) to generalize the condition.
?= One character.*= Sequence.
Also, you can write ~ to find symbols * or ?.
Let’s look at a simple formula
=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: сells are more than or less than
Let’s find out some values using operators: >, <, <>, =. We write it next to the criterion in quotation marks. Thanks to them, we can find cells with numbers that are more or less, equal, or not equal to some value. For example, let’s 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?
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 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 the wildcards (
?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 (&).
- The function returns the same value if the words are written in uppercase or lowercase letters. It doesn’t take into account case strings. You should rename the word if it has a different meaning.
- Pay attention to the spelling of words and letters. The function does not differentiate case strings and doesn’t work with misspellings.
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.
