Avoid the filter to count rows
When you want to count the number of rows corresponding to a specific criteria, many users use the tool Filter.
For 1, 2 or 3 different criteria, you can work like that. But if you have to extract a lot of count of rows for many different criteria, the filter is not the good tool.
Purpose of the COUNTIFS function
The COUNTIFS function counts the number of rows corresponding to one or many criteria.
The COUNTIFS has been introduced since Excel 2007 and allows you to add between 1 or 255 criteria.
You can also use the COUNTIF function but this function only use 1 criteria.
Argument for COUNTIFS
The function COUNTIFS is very easy to build 😎
- Select the first range of cells where is want to filter
- Write the value of your criteria
- and so on for the other criteria...
COUNTIF vs COUNTIFS
The difference is just the number of criteria
- With COUNTIF you can count the rows for one criterion
- With COUNTIFS you can count the rows for 256 criteria
The construction is the same, you just add more criteria
Example with 2 criteria
How many single men are in your data-set?
- 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 criteria
- Select the range E2:E20
- Write the value "Single"
Watch your data ranges
If the function COUNTIFS returns #/VALUES!, is because you haven't selected the same range of cells for each criteria.
Replace the criteria with reference
Instead of writing the value of the 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)