Excel COUNTIFS is an advanced version of the COUNTIF function. Its main purpose is to count cells using multiple criteria. Along with the Excel IF function and the SUMIFS function, it is widely used for data analysis and statistics.
Excel COUNTIFS Function
Let’s look at the basic definition and usage of COUNTIFS.
Basic Definition
This function counts the number of cells that meet multiple criteria across specified ranges.
Basic Usage
The COUNTIFS function allows up to 255 characters. You can use multiple criteria within the formula.
In the data below, we will count the cells that meet two text criteria.
We counted the number of Inner products sold by Steve.
The formula used is as follows.
G3 cell =COUNTIFS(B3:B13,”steve”,C3:C13,”inner”)
Excel COUNTIFS Examples
Let’s look at how to use logical operators and cell values as criteria.
Using Logical Operators
Logical operators are one of the key features of Excel conditional functions.
In the data below, we will count the cells that meet two numeric criteria.
We counted the cells with a price of 200 or more and sales quantity of 1,000 or more.
The formula used is as follows.
G3 cell =COUNTIFS(D3:D13,”>=200″,E3:E13,”>=1000″)
Using Cell Values as Criteria
Using cell values in Excel conditional functions is one of the key features of formulas.
In the data below, we will count cells using the values in two cells as criteria.
We used the value in cell G5 for the price and the value in cell G7 for the sales quantity.
The formula used is as follows.
G3 cell =COUNTIFS(D3:D13,”>=”&G5,E3:E13,”>=”&G7)
Excel COUNTIFS Conclusion
The COUNTIFS function is useful for counting cells that meet multiple criteria simultaneously. For example, you can use COUNTIFS to count sales of a specific product in a specific region during a specific period in a database. To do this, specify the ranges and comparison criteria for each condition, such as a date range, region, and product.
The COUNTIFS function can use multiple criteria, separated by commas. When specifying criteria, you must specify both the range and the comparison criterion. Comparison criteria can use an equal sign (=), inequality signs (<, >), less than or equal to (<=), greater than or equal to (>=), or combined inequality and equal signs (such as <>).
The COUNTIFS function is very useful for extracting the information you need from a database. For example, you can use COUNTIFS to count sales of a specific product in a specific region during a specific period. To do this, specify the ranges and comparison criteria for each condition, such as a date range, region, and product. You can also use it with the COUNTIF function, which counts cells that meet any one of multiple criteria, to create more complex conditions.