Google Sheet countif only visible data using sumtotal

user_3022331 0
Viewed 1528
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

enter image description here

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.

subtotal countif counting google-sheets sum
1 Answers

add column of SUBTOTAL

=SUBTOTAL(103, B4)

enter image description here

hide it:

enter image description here

then use:

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

enter image description here

=COUNTIF(D4:D, 1)

enter image description here

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

enter image description here

user_56326290
Related