The function COUNTIFS returns the number of rows found in your document for a specific lookup
- The column containing the first criterion
Select the column where is the first criterion
- First criterion
Write the value of the first criterion
- [optional] The column containing the second criterion
Select the column where is the second criterion
- [optional] Second criterion
Write the value of the second criterion
=COUNTIFS(Column with the first criteria, First criteria, Column with the second criteria, Second criteria, ....)
Don't count your rows with the filter ⛔⛔⛔
When you want to count the number of rows corresponding to specific criteria, many users use the Filter.
Why not for one or two research? But for more, it's a waste of time and you must use another technique.
COUNTIF vs COUNTIFS in Excel
There is no difference between these 2 functions in terms of performance. The only difference is the number of criteria you can fill.
- With COUNTIF you can count the rows for 1 criterion
- With COUNTIFS you can count the rows for 255 criteria
The construction is the same, you just add more criteria 😉
Example to count rows 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 criterion.
Replace the criteria with a 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 logical symbols like greater than (>) or lower than (<). Have a look at this article to build your COUNTIFS function in this situation.