Usefulness of the COUNTIFS function
The COUNTIFS function allows you to count the number of rows corresponding to criteria.
In fact, it's a bit like filtering your data and counting the rows that match your selection criteria. Except here, it's the function that returns this number for you 👍😍😎
COUNTIFS allows you to count between 1 and 255 criteria. The COUNTIF function can only do this on one criterion.
Repro the formula here
Throughout this article, you will be able to try the different formulas in this workbook. It's not an image, it's a workbook 👍🏆
The formula COUNTIFS
The function COUNTIFS is very easy to build 😎
- Selecting a range of cells where is your criterion (the value you look for)
- The selection criterion for this data range
- and so on for the other 255 criteria...
If you select more than one range, you must select the same number of rows.
Example with 2 criteria
If you want to know how many single men are in your dataset, follow these steps
- Start by writing the formula COUNTIFS
- Select the range of cells where you want to search your criterion (the column C)
- Enter the value,or the text, you want to search (here "Man").
The word "Man" is written between double-quotes because it's a string and all strings must be written between double-quotes in Excel.
Then, for the second criterion
- Select the range E2:E20
- Write the criterion "Single"
Note: It is compulsory that the range of your cell are exactly the same dimension. ⚠⚠⚠
For example COUNTIFS= C2:C20,"Man",E2:E21,"Single") will return the #/VALUE! because the ranges are not equal.
Replace the criteria by reference
Instead of writing your criteria directly in the formula, you can use the contain of a cell by using the reference of the cell.
Let's go deeper
As you can see, it's very easy to count the number of rows with the function COUNTIFS. Now let's see how we can create more sophisticate criteria.😎
Greater than / Less than
You can enhance your criteria by adding logical operator. In other word, you can select all the rows where one criterion is greater than a value.
For example, if we want to determine the number of men with incomes greater than or equal to 50,000. The formula is
If you use the reference of a cell, your formula is
⚠⚠⚠The logical operator must be written between double quotes.
The symbol & is needed to link the logical operator and the cell reference. Without this symbol, the criterion can't be understand.
Select a range of data
If you want to return the number of men who has an income between 50,000 and 70,000, you must write your function with 3 criteria
- One for the criterion of the man
- The second for the income greater than 50,000
- The third, on the same range of cells, for the value lower than 70,000
The formula is
How to do a OR with COUNTIFS
Now, if you want to count the number of rows for the women single or divorced. Here, you have no other choice than to create 2 formulas. One is for the single women, and the second for the divorced women.
Selection on a part of the criteria
With a computer, you can perform a research on a part of a text string.
- You can perform a research to replace one character by ? (T?m for Tim or Tom)
- Or with a * for any number of characters
Most of the time, you will use *. The ? is not really useful
If you want to find all the people with a name starting with a S, you will write the following formula
There is 2 person (Samen and Satake)
Now if you want to find all the people with a Y in the name, you will write this formula
And now, there is 5 people (Champney, Byerly, Gaudefroy, Fordyce, Freyer)