Search code examples
google-sheetssumcountingcountifsubtotal

Google Sheet countif only visible data using sumtotal

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.

Solution

  • 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