Excel Tip: How to count data in a filtered rows

Printer-friendly versionPrinter-friendly versionSend by emailSend by emailPDF versionPDF version

In Microsoft Excel, there are several ways to count data in rows and columns. However, when cells in columns are filtered, the counting is inaccurate because hidden cells are still counted. This article will solve this kind of scenario.

How to count filtered row or column data in Excel

For the sake of this demo, consider a list of students in a batch of senios students. The list has the columns Last Name, Family Name, M.I., Gender and Class/Section. Then you add a "Filter" for these columns as shown below:

Sample filtered columns

If you want to use a formula to count the number of Males or Females in the list above, you can use formulas like the following:

To count the Males:

=COUNTIF(E6:E45,"=Male")

To count the Females:

=COUNTIF(E6:E45,"=Female")

The COUNTIF formula works here because Excel will only count the student if the value in column E is Male or Female, thus students are counted based on gender correctly.

However, the problem will start to occur when the data in the columns are being filtered. The problem is that, when the data are filtered, the hidden data are still counted thus giving an incorrect result.

To avoid or resolve this problem, a complex SUMPRODUCT formula is required. In the sample data that I created for this demo, the formula required to count the Males regardless if the Gender column is filtered is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E6:E45,ROW(E6:E45)-ROW(E6),0,1)),--(E6:E45="Male"))

To correctly count the number of Females even though the Gender column is filtered, the formula is:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(E6:E45,ROW(E6:E45)-ROW(E6),0,1)),--(E6:E45="Female"))

Using the SUMPRODUCT formula above, when the columns are filtered, the hidden cells are no longer counted so the results are accurate. Download the sample file below.

 

MS Office Software: 
AttachmentSize
File Counting-Filtered-Cells-Sample.xlsx12.49 KB