Search code examples
google-sheetslambdapercentagecountif

How to count values in a column based on condition in another column in google sheet

How can I count number of positive results in column Profit/Loss if only setup is ob? I want to store the result in another cell where I can monitor percentage of my trade based on the setups So for example in this Table I would get 2 of 3 trade is successful if setup is ob.

Data sample

Date Profit/Loss Bias Setup
31/10/2022 $1,000.00 Up fvg
01/11/2022 -$200.00 Down ob
02/11/2022 $2,000.00 Up br
03/11/2022 -$150.00 Up mt
04/11/2022 $1,500.00 Up fvg
05/11/2022 $1,500.00 Down ob
06/11/2022 $2,000.00 Up mt
07/11/2022 -$200.00 Down br
08/11/2022 $3,000.00 Down fvg
09/11/2022 -$1,000.00 Down fvg
10/11/2022 $2,000.00 Down ob
11/11/2022 $3,000.00 Up fvg
Solution

  • Use Countifs() function.
    To get the percentage of the number of Profit/Loss values > 0 and setup = "ob" Divided by the count of instances of setup = "ob".

    =COUNTIFS(B3:B,">0",D3:D,"=ob")/COUNTIFS(D3:D,"=ob")

    enter image description here

    Using Lambda

    =LAMBDA(PnL, Setup, TO_PERCENT(COUNTIFS(PnL,">0",Setup,"=ob")/COUNTIFS(Setup,"=ob")))
       (B3:B,D3:D)

    enter image description here

    Used formulas help
    LAMBDA - COUNTIFS - TO_PERCENT