How to Count Excluding Hidden Cells in Excel

Introduction

Excel is a powerful tool for data analysis, but one common task is to count cells excluding those that are hidden by filters or manual hiding. This tutorial explains how to accomplish this using the `SUBTOTAL` and `AGGREGATE` functions.

Method 1: Using the SUBTOTAL Function

To count the number of cells that are not hidden, you can use the `SUBTOTAL` function. This function is especially useful in combination with filters.

For counting numbers:

SUBTOTAL(102, A:A)

For counting non-empty cells:

SUBTOTAL(103, A:A)

Method 2: Manually Excluding Hidden Cells

Alternatively, you can manually exclude hidden cells by highlighting the range, going to the 'Tools' tab, and counting the range with an option to exclude hidden cells.

1. Highlight the area of cells you want to count.

2. Go to the 'Tools' tab and click on 'Count'.

3. A message will appear asking if you want to exclude hidden cells. Click 'Yes' to exclude them.

4. Another message may appear asking if you want to count cells with no value listed. You can choose 'Yes' or 'No' based on your needs.

5. Place your cursor outside the highlighted area and click 'Count'.

Method 3: Using SUMPRODUCT with SUBTOTAL

If you want to exclude rows hidden by filters, especially for more complex criteria, the `SUMPRODUCT` function in combination with `SUBTOTAL` is a viable solution.

Here’s a sample formula:

SUMPRODUCT(B2:B183, SUBTOTAL(3, OFFSET(A2, ROW(A2:A18)-ROW(A2), 0, 1)))

Explanation:

`SUMPRODUCT` is used to test B2:B18 for values greater than or equal to 3. `SUBTOTAL(3, OFFSET(A2, ROW(A2:A18)-ROW(A2), 0, 1))` checks cells in column A that are not hidden by a filter. You can add more Boolean criteria by following the structure of the `SUMPRODUCT` formula.

For further simplification, the `OFFSET` part of the formula can be simplified as:

SUMPRODUCT(B2:B183, SUBTOTAL(3, OFFSET(A2, ROW(A2:A18)-ROW(A2), 0)))

Note: The `SUBTOTAL` function typically does not work within array formulas, but the use of `OFFSET` allows it to work effectively.

Conclusion

Counting cells while excluding hidden ones is an essential skill in Excel, particularly when working with large datasets or data that requires advanced filtering. The `SUBTOTAL` function is one of the most effective tools for this task, especially when used in combination with array formulas like `OFFSET` and `SUMPRODUCT`.