Google Sheet countif only visible data using sumtotal

Question

Sell / Buy
Buy
Buy
Sell

Hi all, I have google sheet that has a lot of Data However, i want to count number of "Buy" and "Sell" based on filtering the dates NOT the whole column ONLY VISIBLE rows

The code i'm trying to use is the below

=SUMPRODUCT(SUBTOTAL(9,OFFSET(E4,ROW(E4:E100)-ROW(E4),,1)),--(E4:E100 = "Buy"))

i'm really having bad time solving this issue, i would greatly appreciate if someone can help me with the code.

thank you.

question from:https://stackoverflow.com/questions/65642280/google-sheet-countif-only-visible-data-using-sumtotal

Answer 1

add column of SUBTOTAL

=SUBTOTAL(103, B4)

hide it:

then use:

=COUNTA(IFNA(FILTER(B4:B, C4:C="buy", D4:D=1)))

=COUNTIF(D4:D, 1)

="Buy: "&  COUNTIFS(C4:C, "buy",  D4:D, 1)&
 " Sell: "&COUNTIFS(C4:C, "sell", D4:D, 1)