Excel COUNTIF and COUNTIFS Functions are powerful tools designed to streamline your data analysis process by providing quick, precise counts based on specific criteria. Whether you’re tallying up customer feedback, analyzing sales data, or managing inventory, these functions offer the flexibility and efficiency to handle complex counting tasks with ease. This guide delves into the functionalities of Excel COUNTIF and COUNTIFS, illustrating how you can leverage these functions to transform your data handling experience, ensuring accurate results and insightful reports with minimal effort.
There are numerous functions in Excel that require the user to enter one or more criteria in order to obtain the desired outcome. Use Excel’s COUNTIF or COUNTIFS functions, for instance, to count cells in excel based on a variety of criteria.
The COUNTIF and COUNTIFS functions in Excel are discussed in this tutorial in different contexts for using a single or multiple criteria.
Although the COUNTIF and COUNTIFS functions will receive the majority of my attention in this tutorial, all of these examples can also be applied to other Excel functions that accept numerous criteria as inputs (such as SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS).
This Tutorial Covers:
- An overview of the COUNTIF and COUNTIFS functions in Excel
- COUNTIF function in Excel with Single Criteria
- COUNTIF function in Excel with Multiple Criteria
- Using Excel COUNTIF Functions with NUMBER Criteria
- Example-1 Count Cells When a Value and a Criteria Are Equal
- Example-2 Count Cells when a Value Is higher a Criteria
- Example-3 Count Cells when a Value is below a Criteria
- Example-4 Count Cells Having More Than One Criteria and Between Two Values
- Using Excel Functions’ TEXT Criteria
- Example-1 Count Cells when a Specified String Is Equal to that same Criteria
- Example-2 Count Cells If the Criteria is NOT Equal to the Specified String
- Using the COUNTIF and COUNTIFS functions in Excel with DATE Criteria
- Example-1 Count Cells When a Specific Date Is the Matched Criteria
- Example-2 Count Cells when Criteria is BEFORE or AFTER to a Specified Date
- Example-3 Count Cells with Multiple Criteria – Between Two Dates
- Using Wildcard Characters as Criteria in COUNTIF and COUNTIFS Functions
- Example-1 Count Cells that contain Text
- Example-2 Count Non-blank Cells
- Example-3 Count Cells that contain specific text
1. An overview of COUNTIF and COUNTIFS functions in Excel
Let’s first learn how to use the Excel COUNTIF and COUNTIFS tools to do excel count cells.
- COUNTIF function in Excel (takes Single Criteria)
The COUNTIF function in Excel works best in circumstances where you want to count cells according to a specific criterion. Use the COUNTIFS function to count depending on multiple criteria.
Syntax:
=COUNTIF(range, criteria)
Input Arguments:
range – the cell range that you want to enumerate.
criteria – the standards that must be examined in comparison to the available cells in order for a cell to be tallied.
- COUNTIFS function in Excel (takes Multiple Criteria)
The COUNTIFS function in Excel works best when you want to tally cells based on a variety of factors.
Syntax:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
Input Arguments:
criteria_range1 – The quantity of cells you want to compare to criteria for.
criteria1 – the criteria that you want to assess in order to choose which cells to tally in criteria_range1.
[criteria_range2] – The range of cells you want to compare to the parameters for.
[criteria2] – The criteria that you want to assess in order to choose which cells to tally in criteria_range2.
2. Using Excel COUNTIF Functions with NUMBER Criteria
Let’s now look at some examples of how to use COUNTIF with multiple criteria in Excel.
- Example-1 Count Cells When a Value and a Criteria Are Equal
Either directly enter the criteria or use the cell reference that includes the criteria to get the number of cells where the criteria argument equals a given value.
The example that follows shows how to enumerate the cells that contain the number 20. (which means that the criteria argument is equal to 20).
Here is the formula:
=COUNTIF($B$2:$B$11,D2)
The criteria is located in cell D2 of the sample shown above (in the image). The criteria can also be entered straight into the formula. You could, for instance, use:
=COUNTIF($B$2:$B$11,20)
- Example-2 Count Cells when a Value Is higher a Criteria
Using the greater than operator (“>”), we can determine count the number of cells that contain a value larger than a given value. Either explicitly in the formula, or by referencing a cell that meets the requirements.
Excel requires that we enclose any function we use in a criterion with double quotes. For instance, if the criterion is higher than 15, we must enter “>15” as the requirement (see illustration below):
Below is the formula:
=COUNTIF($B$2:$B$11,”>15″)
The criteria can also be contained in a cell, with the cell identifier serving as the criteria. You DO NOT need to surround the parameters in double quotes in this instance:
=COUNTIF($B$2:$B$11,D2)
Additionally, there may be instances where you want the conditions to be in a cell but not with the operator. For instance, you might want the value 15 and not >15 in column D2.
If so, you must construct a criteria argument that combines an operator and a cell reference (see illustration below):
=COUNTIF($B$2:$B$11,”>”&D2)
NOTE: The operator is always enclosed in double quotations when used with a cell reference. An & separates the operator and cell reference (&).
- Example-3 Count Cells when a Value is below a Criteria
Excel requires that we enclose any function we use in a criterion with double quotes. For instance, if the requirement is that the number must be less than 10, we must write “10” as the requirement (see illustration below):
=COUNTIF($B$2:$B$11,”<10″)
The criteria can also be contained in a cell, with the cell identifier serving as the criteria. The
following does NOT require that the requirements be enclosed in double quotes:
=COUNTIF($B$2:$B$11,D2)
Additionally, there may be circumstances in which you want the parameters to be in a cell but not with the operator. For instance, you might want 10 to appear in column D2 instead of <10.
If so, you must construct a criteria argument that combines an operator and a cell reference:
=COUNTIF($B$2:$B$11,”<“&D2)
NOTE: The operator is always enclosed in double quotations when used with a cell reference. An & separates the operator and cell reference (&).
- Example-4 Count Cells Having More Than One Criteria and Between Two Values
The COUNTIF function requires the use of numerous criteria in order to obtain a count of values between two values.
Here are two ways to accomplish this:
- Utilizing the COUNTIFS method is METHOD 1
- Utilizing two COUNTIF methods is METHOD 2
Utilizing the COUNTIFS method is METHOD 1
Multiple criteria can be passed to the COUNTIFS function, and it only tallies the cells when all of the arguments are TRUE. Use the COUNTIFS function to count cells that have values between two given values, such as 10 and 15.
=COUNTIFS($B$2:$B$11,”>10″,$B$2:$B$11,”<15″)
NOTE: Cells with 10 or 15 do not count when using the formula above. Use the greater than equal to (>=) and less than equal to (=) operators to include these columns. Here is the equation:
=COUNTIFS($B$2:$B$11,”>=10″,$B$2:$B$11,”<=15″)
These requirements may also be contained in cells, with the cell citation serving as the requirement. The following does NOT require that the requirements be enclosed in double quotes:
=COUNTIFS($B$2:$B$11,D2,$B$2:$B$11,E2)
Additionally, you can combine cell references with operators (where the operator is entered directly in the formula). When a cell reference and an operator are combined, the operator is always enclosed in double quotations. An & separates the operator and cell reference (&).
=COUNTIFS($B$2:$B$11,”>”&D2,$B$2:$B$11,”<“&E2)
Utilizing two COUNTIF methods is METHOD 2
If your criteria are numerous, you can either use COUNTIFS or combine COUNTIF functions. The following formula would accomplish the same result:
=COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,”>15″)
The count of cells with a value greater than 15 is subtracted from the number of cells with a value higher than 10 in the formula above. As a consequence, we would get the number 2. (which is the number of cells that have values more than 10 and less than equal to 15).
Use the following formula if you want the formula to consider both 10 and 15:
=COUNTIF($B$2:$B$11,”>=10”)-COUNTIF($B$2:$B$11,”>15”)
Use the following algorithm if you want to prevent the counting of both “10” and “15”:
=COUNTIF($B$2:$B$11,”>=10”)-COUNTIF($B$2:$B$11,”>15”)-COUNTIF($B$2:$B$11,10)
These criteria may be contained in cells, and you may use references to those cells, or you may use a mix of operators and references to those cells.
We can easily use that text as the criterion to count specific text cells. In the part after, two examples are discussed.
- Example-1 Count Cells when a Specified String Is Equal to that same Criteria
If I want to enumerate every cell in the dataset (pictured below) that contains the name David, I can do so by using the formula below:
=COUNTIF($B$2:$B$11,”David”)
I must enclose the text parameters in double quotes because this is a text string.
The following example illustrates how you can store the criteria in a cell and then mention that cell:
=COUNTIF($B$2:$B$11,E2)
NOTE: If there are leading or trailing spaces in the criteria or criteria range, incorrect results may occur. Before applying these algorithms, make sure the data has been cleaned.
- Example-2 Count Cells If the Criteria is NOT Equal to the Specified String:
You can count cells that don’t contain a specific text, just like we saw in the sample above. The not equivalent to operator (>) must be used in order to accomplish this.
Here is the formula to do it if you want to enumerate all the cells that do not contain the name David:
=COUNTIF($B$2:$B$11,”<>David”)
The criteria can also be contained in a cell, with the cell identifier serving as the criteria. The following does NOT require that the requirements be enclosed in double quotes:
=COUNTIF($B$2:$B$11,E2)
Additionally, there may be instances where you want the conditions to be in a cell but not with the operator. For instance, you might want the name David and not <>David to appear in column E2.
If so, you must construct a criteria argument that combines an operator and a cell reference (see illustration below):
=COUNTIF($B$2:$B$11,”<>”&E2)
When a cell reference and an operator are combined, the operator is always enclosed in double quotations. An & separates the operator and cell reference (&).
4. Using the COUNTIF and COUNTIFS functions in Excel with DATE Criteria
Date and time are kept as integers in Excel. The same as we do with numerals, we can use it.
- Example-1 Count Cells When a Specific Date Is the Matched Criteria
We would use the equivalent to operator (=) and the given date to obtain the number of cells that contain the given date.
I advise using the DATE function when using the date because it eliminates the chance of a date number error. As an illustration, I could use the DATE method as shown below to use the date February 1, 2023:
=DATE(2015,2,1)
The same date would be returned by this formula despite localized variations. Using the US date syntax, 01-02-2023 would be February 1, 2023, while using the UK date script, it would be January 2, 2023. But February 1, 2023, would always be the result of this algorithm.
The following algorithm can be used to determine how many cells contain the date 02-02-2023:
=COUNTIF($A$2:$A$11,DATE(2023,2,2))
- Example-2 Count Cells when Criteria is BEFORE or AFTER to a Specified Date
The less than/greater than operators can be used to measure the number of cells that contain dates that are either before or after a given date.
For instance, I can use the formula below to tally all the cells that have a date after February 3, 2023.
=COUNTIF($A$2:$A$11,”>”&DATE(2023,2,3))
The number of cells prior to a given date can also be counted in a similar manner. Use the “equal to” operator in addition to the “greater than/less than” operator if you want to tally a date.
A cell reference containing a timestamp can also be used. The operator must be enclosed in double quotations in this instance, and the date must be combined with an ampersand (&).
See example below:
=COUNTIF($A$2:$A$11,”>”&F2)
- Example-3 Count Cells with Multiple Criteria – Between Two Dates
The COUNTIF function requires the use of numerous criteria in order to obtain a count of values between two values.
One COUNTIFS function or two COUNTIF functions can be used to accomplish this.
METHOD 1: Using the COUNTIFS method
Multiple criteria may be passed as arguments to the COUNTIFS function, which only tallies the cells when all the criteria are true. Use the COUNTIFS function to count cells that have values between two dates (for example, February 1 and February 5):
=COUNTIFS($A$2:$A$11,”>”&DATE(2023,2,1),$A$2:$A$11,”<“&DATE(2023,2,5))
Cells with the stated dates do not count in the formula above. Use the greater than equal to (>=) and less than equal to (=) operators to include these times as well. Here is the equation:
=COUNTIFS($A$2:$A$11,”>=”&DATE(2023,2,1),$A$2:$A$11,”<=”&DATE(2023,2,5))
The dates may also be contained in a cell, and the cell citation may be used as the criterion. In this situation, the operator with the date in the cells is not allowed. You must explicitly add operators (in double quotes) and cell references (using a &) to the formula (&).
See the image below:
=COUNTIFS($A$2:$A$11,”>”&F2,$A$2:$A$11,”<“&G2)
METHOD 2: Using COUNTIF functions
If your criteria are numerous, you can either combine two COUNTIF functions into one or use one COUNTIFS function. The following method would also work:
=COUNTIF($A$2:$A$11,”>”&DATE(2023,2,1))-COUNTIF($A$2:$A$11,”>”&DATE(2023,2,5))
In the formula above, we first tally the cells with dates that are after February 1 and then subtract the number of cells with dates that are after February 5. With this, we would get the number 6. (which is the number of cells that have dates after February 1 and on or before February 5).
Use the following formula if you don’t want February 1 and February 5 to be included in the calculation:
=COUNTIF($A$2:$A$11,”>=”&DATE(2023,2,1))-COUNTIF($A$2:$A$11,”>”&DATE(2023,2,5))
Use the following formula to prevent both days from being counted:
=COUNTIF($A$2:$A$11,”>”&DATE(2023,2,1))-COUNTIF($A$2:$A$11,”>”&DATE(2023,2,5)-COUNTIF($A$2:$A$11,DATE(2023,2,5)))
Additionally, you can use the references from the cells and have the qualifying dates in cells (along with operators in double quotes joined using ampersand).
5. Using Wildcard Characters as Criteria in COUNTIF and COUNTIFS Functions
In Excel, the following three symbols are wildcards:
- (asterisk) – It can stand in for any quantity of letters. Ex*, for instance, could also stand for exceed, excels, example, expert, etc.
- ? (question mark) – It stands for just one letter. Tr?mp, for instance, could refer to Trump or Tramp.
- ~ (tilde) – In the text, it is used to indicate a wildcard symbol (, *,?)
When other built-in count functions don’t work, you can enumerate cells using the COUNTIF function and wildcard characters.
Consider the following data collection as an illustration:
Let’s now cover several examples in the next following section.
- Example-1 Count Cells that contain Text
Using the wildcard character *(asterisk), we can enumerate cells that contain text. Asterisk counts all cells with text in them because it can represent any amount of characters.
Here is the formula:
=COUNTIFS($C$2:$C$11,”*”)
Note: The aforementioned formula disregards cells with numbers, blank spaces, and logical values but counts cells with apostrophes (which make them look blank) or cells with empty strings (=””) that may have been returned as a result of a formula.
- Example-2 Count Non-blank Cells
Think twice before using the COUNTA method.
If you try it, it might not work. The empty string, which is frequently returned by formulas as =”” or when users input only an apostrophe in a cell, will also be counted by COUNTA. Empty string cells appear blank but are not, and the COUNTA method therefore counts them.
The empty string, which is frequently returned by formulas as =”” or when users input only an apostrophe in a cell, will also be counted by COUNTA. Empty string cells appear blank but are not, and the COUNTA method therefore counts them.
Therefore, when you use the formula =COUNTA(A1:A11), it gives you 11, when it should give you 10.
To count non-blank cells in excel use the formula below:
=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)-SUMPRODUCT(-ISLOGICAL($A$1:$A$11))
Let’s dissect this formula to better comprehend it:
- COUNTIF($N$8:$N$18,”?*”) – The formula’s result for this portion is 5. Any cell with a text letter in it is included in this. One character is denoted by A ?, while any amount of characters is denoted by *. Therefore, Excel is forced to count cells that contain at least one text character by the combination ?* in the criteria
Each compartment that contains a number is counted. It gives three in the example above.
This counts all of the cells with logical values. It gives 2 in the example above.
- Example-3 Count Cells that contain specific text
Say we want to tally every cell where the sales representative’s name starts with the letter D. The COUNTIF function’s wildcard symbol can be used to accomplish this with ease.
Here is the formula:
=COUNTIFS($C$2:$C$11,”D*”)
The cell’s text must start with J and can have any amount of characters, according to the criteria D*.
Anywhere in the text where you want to count cells that contain the alphabet, surround it with an asterisk on both sides. Use *o* as the criterion, for instance, if you want to tally cells that have the letter “o” in them.
Application of COUNTIF and COUNTIFS Functions
in Excel
Here are some uses of these functions:
- Conditional Data Counting:
- COUNTIF allows you to count the number of cells that meet a single condition (e.g., counting the number of sales transactions that exceed a certain value), while COUNTIFS can handle multiple criteria simultaneously (e.g., counting sales transactions that exceed a certain value in a specific region).
- Error Checking and Data Validation:
- Use these functions to identify and count instances of errors or inconsistencies in your data, such as counting the number of cells that contain text in a column supposed to have only numerical values.
- Performance Analysis:
- Analyze performance by counting the number of times a specific value appears, such as the number of employees meeting or exceeding sales targets, or the number of products achieving certain quality scores.
- Frequency Distribution Creation:
- Create frequency distributions by counting how often specific values occur within a data set, aiding in statistical analysis and visualization of data distributions.
- Survey or Poll Analysis:
- Efficiently analyze survey or poll responses by counting the number of responses for each option or combination of options, providing quick insights into public opinion or feedback.
- Attendance or Participation Tracking:
- Track attendance or participation by counting the number of times individuals appear in a list, such as students attending a class or employees attending mandatory training sessions.
COUNTIF and COUNTIFS functions are highly versatile for various data analysis scenarios, providing a straightforward method to count cells that match specific criteria, thereby enhancing data analysis efficiency and accuracy.
You may be interested:
