Formula to count visible rows only with criteria

You can count visible rows in a filtered list with this formula. But you need to count visible rows, and with criteria, this article will help you.

To count visible rows only with criteria, you can use a rather complex formula based on SUMPRODUCT, SUBTOTAL, and OFFSET function.

The formula: =SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(firstrow,ROW(range)-MIN(ROW(range)),0))))

  • Use SUMPRODUCT function to apply both the SUBTOTAL function (via OFFSET) and the criteria.
  • range represents a range of cells