What is COUNTIF function in Excel?
The COUNTIF function is one of the Statistical functions of Excel.
It counts the number of cells within a range that meet the given condition.
We can find this function in Statistical category of the insert function Tab.
How to use COUNTIF function in excel
- Click on an empty cell (like F5).
2. Click on the fx icon (or press shift+F3).
3. In the insert function tab you will see all functions.
4. Select STATISTICAL category.
5. Select COUNTIF function.
6. Then select ok.
7. In the function arguments Tab you will see COUNTIF function.
8. Range is the range of cells from which you want to count nonblank cells.
9. Criteria is the condition in the form of a number, expression, or text that defines which cells will be counted.
10. You will see the results in the formula result section.
Examples of COUNTIF function in Excel
Here are 10 examples of the COUNTIF function in Excel:
- =COUNTIF(A1:A10,”Yes”) – counts the number of cells in range A1:A10 that contain “Yes”.
- =COUNTIF(B1:B5,”<5″) – counts the number of cells in range B1:B5 that are less than 5.
- =COUNTIF(C1:C8,”apple“) – counts the number of cells in range C1:C8 that contain the word “apple”.
- =COUNTIF(D1:D6,”>=0″) – counts the number of cells in range D1:D6 that are greater than or equal to 0.
- =COUNTIF(E1:E9,”<>”) – counts the number of non-blank cells in range E1:E9.
- =COUNTIF(F1:F7,”=TRUE()”) – counts the number of cells in range F1:F7 that contain the value TRUE.
- =COUNTIF(G1:G12,”=A*”) – counts the number of cells in range G1:G12 that start with the letter “A”.
- =COUNTIF(H1:H5,”<=10%”) – counts the number of cells in range H1:H5 that are less than or equal to 10%.
- =COUNTIF(I1:I10,”<>Red”)+COUNTIF(I1:I10,”<>Blue”) – counts the number of cells in range I1:I10 that do not contain either “Red” or “Blue”.
- =COUNTIF(J1:J8,A1) – counts the number of cells in range J1:J8 that contain the same value as cell A1.
The COUNTIF function
The COUNTIF function is a built-in function in Microsoft Excel that allows users to count the number of cells within a range that meet a specific criteria or condition.
Syntax of the COUNTIF function
The syntax of the COUNTIF function is =COUNTIF(range, criteria), where the ‘range’ argument refers to the range of cells that you want to apply the criteria to, and the ‘criteria’ argument represents the condition that needs to be met in order for a cell to be counted.
Working of the COUNTIF function
The COUNTIF function works by applying specified criteria to a given range of cells and counting how many cells within that range meet that particular criteria.
The function returns the count value as its result.
For example, if we have a dataset containing sales data for various products, we can use the COUNTIF function to count the number of times a specific product appears in the dataset.
Suppose our dataset is in cells A1:B10, where column A contains the names of the products and column B contains their corresponding sales figures. To count the number of times the product “Widget” appears in the dataset, we would use the formula:
=COUNTIF(A1:A10,"Widget")
This formula would return the count of cells within the range A1:A10 that contain the value “Widget”.
Arguments of the COUNTIF function
The two arguments of the COUNTIF function are:
- Range: This argument specifies the range of cells you want to count.
- Criteria: This argument specifies the condition that must be met in order for a cell to be counted.
For example, let’s say we have a range of cells A1:A10 containing numbers and we want to find out how many cells have a value greater than 5. The formula using COUNTIF would be:
=COUNTIF(A1:A10, ">5")
This formula would return the count of cells within the range A1:A10 that contain a value greater than 5.
The COUNTIF Function: Counting Cells Based on Multiple Criteria
Yes, the COUNTIF function can count cells based on multiple criteria by using logical operators such as AND and OR.
For example, if we have a dataset containing sales data for various products and we want to count the number of times the product “Widget” was sold for more than $100, we would use the formula:
=COUNTIFS(A1:A10,"Widget",B1:B10,">100")
This formula uses the COUNTIFS function which allows us to specify multiple criteria by using logical operators.
Using Wildcards with the COUNTIF Function
Wildcards can be used with the COUNTIF function to count cells based on partial matches or patterns.
The asterisk (*) represents any number of characters, while the question mark (?) represents a single character.
For example, if we have a dataset containing customer names and we want to count the number of customers whose last name starts with “Smi”, we would use the formula:
=COUNTIF(A1:A10,"Smi*")
This formula uses the asterisk wildcard to match any characters that follow the “Smi” pattern.
Difference between COUNTIF and COUNTIFS Functions
The COUNTIF function is used to count cells within a single range that meet a specific criteria. The COUNTIFS function, on the other hand, is used to count cells that meet multiple criteria across different ranges.
For example, if we have a dataset containing sales data for various products and we want to count the number of times the product “Widget” was sold for more than $100 in a specific month, we would use the formula:
=COUNTIFS(A1:A10,"Widget",B1:B10,">100",C1:C10,"Jan")
This formula uses the COUNTIFS function to specify multiple criteria across different ranges.
Counting Cells Based on a Range of Values
Yes, the COUNTIF function can count cells based on a range of values by using logical operators such as greater than (>) and less than (<).
For example, if we have a dataset containing test scores for various students and we want to count the number of students who scored between 80 and 90, we would use the formula:
=COUNTIF(A1:A10,">=80")-COUNTIF(A1:A10,">90")
This formula subtracts the number of cells with values greater than 90 from the number of cells with values greater than or equal to 80.
The COUNTIF Function: Counting Cells Based on Text Values
Yes, the COUNTIF function can count cells based on text values by specifying the text value as the criteria argument.
For example, if we have a dataset containing job titles and we want to count the number of times the title “Manager” appears in the dataset, we would use the formula:
=COUNTIF(A1:A10,"Manager")
This formula counts all cells within the range A1:A10 that contain the text value “Manager”.
The COUNTIF Function: Counting Cells Based on Dates or Times
Yes, the COUNTIF function can count cells based on dates or times by specifying the date or time value as the criteria argument.
For example, if we have a dataset containing project completion dates and we want to count the number of projects that were completed before a specific date, we would use the formula:
=COUNTIF(A1:A10,"<01/01/2023")
This formula counts all cells within the range A1:A10 that contain a date earlier than January 1st, 2023.
Ignoring Case Sensitivity When Using the COUNTIF Function for Text Values
To ignore case sensitivity when using the COUNTIF function for text values, we can use the UPPER or LOWER functions to convert all text values to uppercase or lowercase, respectively.
For example, if we have a dataset containing customer names and we want to count the number of customers whose last name is “smith” (regardless of capitalization), we would use the formula:
=COUNTIF(A1:A10,UPPER("smith"))
This formula converts all text values in the range A1:A10 to uppercase before performing the count.
Counting Cells That Contain Errors with the COUNTIF Function
To count cells that contain errors with the COUNTIF function, we can use the ERROR.TYPE function in combination with the COUNTIF function.
For example, if we have a dataset containing numeric values and we want to count the number of cells that contain errors such as #DIV/0!, we would use the formula:
=COUNTIF(A1:A10,"=IFERROR(A1,""Error"")")
This formula uses the IFERROR function to replace any error values in the range A1:A10 with the text value “Error”, which can then be counted using the COUNTIF function.
The COUNTIF Function: Counting Cells Based on Cell Color or Font Color
No, the COUNTIF function cannot count cells based on cell color or font color. To count cells based on these attributes, we would need to use VBA code or a third-party add-in.
Using the COUNTIF Function with Dynamic Ranges
To use the COUNTIF function with dynamic ranges, we can use named ranges or structured references in our formula.
These allow us to refer to ranges that can expand or contract based on changes to our data.
For example, if we have a dataset in columns A and B and we want to count the number of times the value “Complete” appears in column B, we can use the following formula:
=COUNTIF(Table1[Status],"Complete")
This formula uses a structured reference (Table1[Status]) to refer to the range of cells in column B that contain our status values.
The range will automatically adjust if new rows are added to or removed from the table.
The COUNTIF Function: Counting Cells in a Different Worksheet or Workbook
Yes, the COUNTIF function can count cells in a different worksheet or workbook by referencing the sheet or workbook name in the range argument.
For example, if we have a dataset containing sales data in a different worksheet called “Data” and we want to count the number of times the product “Widget” appears in column A, we would use the formula:
=COUNTIF(Data!A1:A10,"Widget")
This formula counts all cells within the range A1:A10 in the “Data” worksheet that contain the value “Widget”.
Counting Cells That Meet a Certain Condition Using the COUNTIF Function and Logical Operators
To count cells that meet a certain condition using the COUNTIF function and logical operators, we can use symbols such as greater than (>), less than (<), equal to (=), or not equal to (<>) in the criteria argument.
For example, if we have a dataset containing test scores and we want to count the number of students who scored above 90, we would use the formula:
=COUNTIF(A1:A10,">90")
This formula counts all cells within the range A1:A10 that contain a value greater than 90.
Using the COUNTIF Function to Count Unique Values
The COUNTIF function can be used to count unique values by combining it with the SUMPRODUCT and COUNTIF formulas.
For example, if we have a dataset in column A containing a list of names and we want to count the number of unique names in the list, we would use the following formula:
=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))
This formula counts all instances of each name in the range A1:A10, divides 1 by the count of each instance, and then sums those values to get the total count of unique names.
Counting Cells That Are Not Empty with the COUNTIF Function
To count cells that are not empty with the COUNTIF function, we can use the “<>” symbol in the criteria argument to specify cells that do not equal an empty value.
For example, if we have a dataset containing customer data and we want to count the number of customers who have provided an email address (i.e., cells that are not empty), we would use the formula:
=COUNTIF(B1:B10,"<>")
This formula counts all cells within the range B1:B10 that are not empty.
The COUNTIF Function: Counting Cells Based on a Date Range
The COUNTIF function can be used to count cells based on a date range by using logical operators such as greater than (>) and less than (<) in the criteria argument.
For example, if we have a dataset containing project completion dates and we want to count the number of projects that were completed between two specific dates, we would use the formula:
=COUNTIF(A1:A10,">=01/01/2023")-COUNTIF(A1:A10,">12/31/2023")
This formula subtracts the number of cells with dates greater than December 31st, 2023 from the number of cells with dates greater than or equal to January 1st, 2023.
Troubleshooting Common Issues with the COUNTIF Function
Common issues with the COUNTIF function include incorrect syntax, using the wrong type of criteria (e.g., text instead of a formula), and referencing cells with different data types.
To troubleshoot these issues, we can check our formula for errors such as misspelled function names or missing brackets.
We can also ensure that our criteria argument is properly formatted and that all referenced cells contain the same data type.
For example, if we have a dataset containing both text and numeric values and we want to count the number of cells that contain the text value “apple”, we might encounter an error if some cells in the range contain numeric values.
To resolve this issue, we can ensure that all cells in the range are formatted as text before using the COUNTIF function.
COUNTIF related functions
- Use COUNT function to count the number of cells in a range that contains numbers.
- Use COUNTBLANK function to count the number of empty cells in a specified range of cells.
- Use COUNTA function to count the number of cells in a range that are not empty.
- Use COUNTIFS function to count the number of cells specified by a given set of conditions or criteria.
