What is COUNTIFS function in Excel?
The COUNTIFS function is one of the Statistical functions of Excel.
It counts the number of cells specified by a given set of conditions or criteria.
We can find this function in Statistical category of insert function Tab.
How to use COUNTIFS 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 COUNTIFS function.
6. Then select ok.
7. In the function arguments Tab you will see COUNTIFS function.
8. Criteria_range1: is the range of cells you want evaluated for the particular condition.
9. Criteria1 is the condition in the form of a number, expression, or text that defines which cells will be counted.
10. Criteria_range2: is the range of cells you want evaluated for the particular condition.
11. Criteria2 is the condition in the form of a number, expression, or text that defines which cells will be counted.
12. You will see the results in the formula result section.
Examples of COUNTIFS function in Excel
here are 10 examples of the COUNTIFS function in Excel:
- Count the number of cells in a range that meet two conditions: =COUNTIFS(A1:A10, “>5”, A1:A10, “<10”)
- Count the number of cells in a range that match a specific value: =COUNTIFS(A1:A10, “apple”)
- Count the number of cells in a range that are not equal to a specific value: =COUNTIFS(A1:A10, “<>apple”)
- Count the number of cells in a range that meet one condition and do not meet another: =COUNTIFS(A1:A10, “>5”, A1:A10, “<>10”)
- Count the number of cells in a range that contain a specific text string: =COUNTIFS(A1:A10, “apple“)
- Count the number of cells in a range that begin with a certain text string: =COUNTIFS(A1:A10, “apple*”)
- Count the number of cells in a range that end with a certain text string: =COUNTIFS(A1:A10, “*apple”)
- Count the number of cells in a range that fall within a specific date range: =COUNTIFS(A1:A10, “>=”&DATE(2021,1,1), A1:A10, “<=”&DATE(2021,12,31))
- Count the number of cells in a range that are between two values: =COUNTIFS(A1:A10, “>5”, A1:A10, “<10”)
- Count the number of cells in a range that belong to a certain category: =COUNTIFS(A1:A10, “fruit”, B1:B10, “red”)
Using the COUNTIFS Function to Count Cells That Meet Multiple Criteria
You can use the COUNTIFS function to count cells that meet multiple criteria by specifying the range of cells to search and the criteria to apply using the following formula: =COUNTIFS(range1, criteria1, range2, criteria2, …)
For example, you can use the COUNTIFS function to count the number of times a product was sold in a particular region during a given time period by using this formula: =COUNTIFS(Region, “North”, Product, “Widget”, Date, “>01/01/2022”, Date, “<12/31/2022”)
Using Wildcards in the Criteria for the COUNTIFS Function
Yes, you can use wildcards in the criteria for the COUNTIFS function. An asterisk (*) is used as a wildcard character to represent any number of characters.
For instance, you can count all cells that contain the word “apple” regardless of whether it appears at the beginning, middle, or end of the cell using this formula: =COUNTIFS(Fruits, “apple“)
Specifying Multiple Criteria in the COUNTIFS Function
To specify multiple criteria in the COUNTIFS function, add additional ranges and criteria to the formula separated by commas.
For example, you can count all cells that meet either of two different criteria using this formula: =COUNTIFS(Range1, Criteria1, Range2, Criteria2)
Where “Range1” is the first range of cells to search, “Criteria1” is the first criterion to apply, “Range2” is the second range of cells to search, and “Criteria2” is the second criterion to apply.
COUNTIFS Function Syntax
The syntax of the COUNTIFS function is as follows: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
In this formula, you need to specify the range of cells to search and the criteria to apply. You can also include additional criteria ranges and criteria pairs as needed.
Using Logical Operators in the Criteria for COUNTIFS
Yes, you can use logical operators like AND and OR in the criteria for the COUNTIFS function.
For example, if you want to count all cells meeting two different criteria, you can use the following formula: =COUNTIFS(range1, criteria1, range2, criteria2)
To count all cells meeting either of two criteria, you can use the following formula: =COUNTIFS(range1, criteria1) + COUNTIFS(range2, criteria2)
Counting Cells Based on Text using COUNTIFS
You can count cells based on the text contained in them by using wildcards with the COUNTIFS function. Here’s an example formula: =COUNTIFS(range, “text“)
This formula counts all cells in the specified range that contain the word “text” anywhere within the cell.
Counting Cells Based on Date or Time using COUNTIFS
You can count cells based on the date or time using the COUNTIFS function by specifying a criteria using quotation marks for dates and times.
For instance, to count all cells that fall between two specific dates, use a formula like this: =COUNTIFS(date_range,”>=01/01/2022″,date_range,”<=12/31/2022″)
You can also specify a criteria using a cell reference that contains a date or time. In this case, use the reference without quotation marks, like this: =COUNTIFS(date_range, A1)
Using Cell References in COUNTIFS Criteria
You can use cell references instead of literal values in the criteria for the COUNTIFS function. For example, to count the number of employees who earn more than a certain salary threshold, use a formula like this:
=COUNTIFS(C2:C10, “>”&D1)
In this formula, the salary threshold is specified using a cell reference to D1.
Counting Cells by Color using COUNTIFS
You can count cells based on their color using the COUNTIFS function with conditional formatting. For example, to count the number of cells in the range A1:A10 that are highlighted in green, use a formula like this:
=COUNTIFS(A1:A10, “*”, A1:A10,CELL(“color”,A1)=3)
In this formula, “*” is used as a wildcard to match any value in the specified range, and “CELL(“color”,A1)=3″ counts only cells that have a background color of green.
Counting Cells by Font Style or Size using COUNTIFS
You cannot use the COUNTIFS function to count cells based on their font style or size. However, you can use VBA macros to accomplish this.
Using COUNTIFS with Non-Contiguous Ranges
You can use the COUNTIFS function with non-contiguous ranges by adding up two or more COUNTIFS functions.
For example, to count the number of employees who work in either the Sales or Marketing departments and earn more than a certain salary threshold, use a formula like this:
=COUNTIFS(B2:B10,”Sales”,C2:C10, “>”&D1)+COUNTIFS(B2:B10,”Marketing”,C2:C10, “>”&D1)
In this formula, two separate COUNTIFS functions are used to count employees who meet each of the desired criteria and their results are added together.
Ignoring Empty Cells in COUNTIFS
You can ignore empty cells when using the COUNTIFS function by specifying a criteria that excludes blank cells.
For example, to count employees who have completed a certain training program but may not have a specific job title, use a formula like this:
=COUNTIFS(B2:B10,”<>”&””, C2:C10,”Completed”)
In this formula, “<>”&”” is used as a criteria to exclude blank cells.
Handling Dynamic Ranges with COUNTIFS
Yes, the COUNTIFS function can handle dynamic ranges by using named ranges or the OFFSET function. For example, to count all sales transactions within a certain date range, use a formula like this:
=COUNTIFS(SalesDate, “>=”&StartDate, SalesDate, “<=”&EndDate)
In this formula, “SalesDate” is a named range that automatically adjusts based on changes to the data.
Counting Cells based on a Partial Match with COUNTIFS
You can count cells based on a partial match using the COUNTIFS function by using wildcards and the “&” operator to concatenate criteria.
For example, to count all cells that contain the word “apple” anywhere within the cell, use a formula like this:
=COUNTIFS(FruitRange, ““&”apple”&”“)
In this formula, “*” is used as a wildcard to represent any number of characters before or after “apple.”
Excluding Certain Criteria from COUNTIFS
You can exclude certain criteria from the COUNTIFS function by using the NOT function to negate the desired criteria.
For example, to count all sales transactions that do not meet a specific dollar threshold, use a formula like this:
=COUNTIFS(SalesAmount, “<“&Threshold)
In this formula, “<“&Threshold specifies a criterion to exclude all cells that are greater than or equal to the threshold.
Using COUNTIFS with Arrays
Yes, you can use the COUNTIFS function with arrays to count cells that meet certain criteria. For example, to count all cells in a range that contain any of several keywords, use a formula like this:
=SUM(COUNTIFS(MyRange,{“apple“,”banana“,”orange“}))
In this formula, an array is used to specify three different criteria for counting cells that contain any of the specified keywords.
Counting Cells based on Length with COUNTIFS
You can count cells based on their length using the COUNTIFS function by using the LEN function to determine the length of each cell.
For example, to count all cells in a range that are exactly 10 characters long, use a formula like this:
=COUNTIFS(MyRange,”??????????”)
In this formula, “?” represents a wildcard character that matches any single character. By repeating it 10 times, we can count cells that are exactly 10 characters long.
Counting Cells based on Value compared to a Reference Value with COUNTIFS
You can count cells based on their value compared to a reference value using the COUNTIFS function by specifying a comparison operator and a reference value.
For example, to count all cells in a range that are greater than the average value of the range, use a formula like this:
=COUNTIFS(MyRange,”>”&AVERAGE(MyRange))
In this formula, “>”&AVERAGE(MyRange) specifies a criterion to count all cells that are greater than the average value of the range.
Counting Cells based on Position within a Range with COUNTIFS
You can count cells based on their position within a range using the COUNTIFS function by combining criteria to specify a specific position.
For example, to count all cells in the first column of a range that are blank, use a formula like this:
=COUNTIFS(MyRange, “”, MyColumnRange, 1)
In this formula, two criteria are specified: the first is a blank cell and the second is that the column number matches the first column of the range.
COUNTIFS 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 COUNTIF function to count the number of cells within a range that meet the given condition.
