The COUNTIF Function in Excel will count rows when a filter is in place.
For those that may not be familiar with this, let me explain.
When filtering data, the rows that do not meet the filter are hidden. However, if you need to count the rows, Excel sees every row within a given range (even if they are hidden). Therefore, using the COUNTIF Function is the best solution.
The syntax for the COUNTIF Function is as follows:
Range – this means one or more cells to count.
Criteria – the criteria in the form of a number, expression, cell reference or text that will be counted.
Using the example on a recent article discussing the IF Function, let’s count the number of Arizona Cardinals players making over $250,000 salary.
NOTE: Once again, these numbers are completely fictional and are only for training purposes.
View the example in the picture above left. The number of players that meet the filter criteria is 12. However, if you use the COUNT Function, the result of the formula will be 16.
The COUNTIF Function would be written as such:
The result of the formula is 12, the correct number of players that have a salary greater than $250,000.
In the above example, we could have easily counted the rows not hidden. But in a real life scenario, there may be hundreds, if not thousands, of rows to count. The COUNTIF Function provides an easy solution for counting the filtered rows.