google sheet countif and filter out other columns if the value is lotto

user_2736467 0
Viewed 56

I have a column

COLUMN H | Column R

trading type | Closed P&L
Lotto | 100%
Lotto | 200%
      | 100%
Day   | -50%

Trying to exclude any rows that have trading type = Lotto and calculate the "non-lotto" winning rate. Curious how could I do so?

So in this case the total non-lotto trades are 2, and only 1 win (>0%) => 50% winning rate.

This doesn't seem to do the jobs

=countif(R2:R148,">0")/COUNT(R2:R165, H:H, "Lotto")

This will return N/A

=countif(R2:R148,">0", H:H, "Lotto")/COUNT(R2:R165, H:H, "Lotto")
google-sheets-formula google-sheets
1 Answers

Use COUNTIFS to get the non Lotto cases where the value is positive and divide it with the total number of non Lotto cases:

=COUNTIFS(H2:H5,"<>Lotto",R2:R5,">0")/COUNTIF(H2:H5,"<>Lotto")

enter image description here

user_112252910
Related