Count filtered rows with additional criteria

asked 2015-12-02 16:08:15 +0100

redart gravatar image

Hi,

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 .....

=SUMPRODUCT((LEFT(BH2956:BH3964)="T")*SUBTOTAL(3,OFFSET($A2956:$A3964,ROW(C1:INDEX($C:$C,ROWS($C2956:$C3964)))-1,,1)))

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.

edit retag flag offensive close merge delete