CODEWITHSUNDEEP
google-sheetssumcountingcountifsubtotal
Deyaa
Deyaa

Reputation: 123

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.

Upvotes: 2

Views: 3758

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 3

Related Questions