The function COUNTIF counts the number of rows for one criterion
- The column containing the criterion
Select the column where is the criterion to select
- The criterion
Value of the criterion or reference of a cell
=COUNTIF(Range of cells, criteria of selection)
Don't use the filters
Most of the Excel users, use the filter to count the rows. But this solution is really not efficient when you have to do this, again and again, for many values.
The solution is to use the COUNTIF function or the COUNTIFS function
How to use COUNTIF
In the following document, you have a list of sales.
You want to know how many times you have sold Banana.
To return the number of times you have the name Banana in your list, you have to write the formula like this
And the result is
Replace the criteria with a cell.
Now if instead of typing the name of the data you want to have the reference of a cell where its value = the name you want
For instance, you can use cell G4 where its value is Banana
Not sensitive case
Don't worry if your criterion doesn't have the same case of the data in your table, the COUNTIF function of Excel, is not a sensitive case.
For instance, in this example, we want to count the number of times we have the word PEACH in uppercase and the result is
More than one criteria
If you need to count rows with more than one criterion you must use the function COUNTIFS. It isn't more complex but you just have to write more arguments 😉