COUNTIFS is one of the most useful function in Excel. The function returns the number of time a word or a value has been found in a list of data.
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.
Use the function COUNTIFS
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 content of a cell by using the reference of the cell 😎😀
Greater than / Lower than
You can create more complex criteria with the logical symbol liker greater than (>) or lower than (<). Have a look at this article to build your COUNTIFS function in this situation.