Count filtered rows with additional criteria


Looking for a formula to count the number of rows in a filtered list that also satisfy an additional, unfiltered, criteria.

In Excel I can do this by combining the SUMPRODUCT and SUBTOTAL functions, as per the following …


This doesn’t work in Calc. Instead of the correct answer 53, it gives 411866. TBH I don’t understand how the Excel formula works - it was offered as a solution on one of the Excel forums. Hoping there’s perhaps a better way to accomplish this in Calc. Thanks.