Discussion Counting in IF stmt in Excel
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/729738#M33727
<P>I have a small Investment spreadsheet. Currently I'm tracking six symbols. One column, H5 - H10, tracks the change for each symbol. I'm trying to do two basic things, count the number of winners and count the number of losers in cells B13 and B14 and secondly, total the winnings and loses in B15 and B16.</P>Sat, 29 Jun 2019 18:25:23 GMTJim-52019-06-29T18:25:23ZCounting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/729738#M33727
<P>I have a small Investment spreadsheet. Currently I'm tracking six symbols. One column, H5 - H10, tracks the change for each symbol. I'm trying to do two basic things, count the number of winners and count the number of losers in cells B13 and B14 and secondly, total the winnings and loses in B15 and B16.</P>Sat, 29 Jun 2019 18:25:23 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/729738#M33727Jim-52019-06-29T18:25:23ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/729795#M33733
<P><LI-USER uid="368751"></LI-USER> </P>
<P>COUNTIF() shall work, but better if you provide small sample file</P>Sat, 29 Jun 2019 20:25:39 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/729795#M33733Sergei Baklan2019-06-29T20:25:39ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730014#M33738
<P><LI-USER uid="521"></LI-USER> </P><P>Thanks for the help.</P><TABLE><TBODY><TR><TD>Group 3A</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>TRADE</TD><TD>TRADE</TD><TD> </TD><TD>OPEN TRADE</TD><TD> </TD><TD>CLOSED</TD><TD> </TD><TD> </TD></TR><TR><TD>ENTRY DATE</TD><TD>SYMBOL</TD><TD>L/S</TD><TD>SHARES</TD><TD>ENTRY PRICE</TD><TD>LAST PRICE</TD><TD>CHANGE</TD><TD>$ GAIN/LOSS</TD><TD>ROI</TD><TD>TRADE P&L</TD><TD>RISK</TD><TD> </TD></TR><TR><TD><TABLE><TBODY><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD></TR><TR><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE><TABLE><TBODY><TR><TD> </TD></TR></TBODY></TABLE></TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>66/24/19</TD><TD>FFXE</TD><TD>L</TD><TD>1</TD><TD>108.26</TD><TD>108.27</TD><TD> $ 0.0</TD><TD> $ 0.01</TD><TD>0.0%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>66/24/19</TD><TD>GLDGLD</TD><TD>L</TD><TD>1</TD><TD>133.5</TD><TD>133.2</TD><TD> $ (0.30)</TD><TD> $ (0.30)</TD><TD>-0.2%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>66/24/19</TD><TD>QQQ</TD><TD>L</TD><TD>1</TD><TD>188.5</TD><TD>186.74</TD><TD> $ (1.76)</TD><TD> $ (1.76)</TD><TD>-0.9%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>66/24/19</TD><TD>TLT</TD><TD>L</TD><TD>1</TD><TD>132</TD><TD>132.81</TD><TD> $ 0.81</TD><TD> $ 0.81</TD><TD>0.6%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>66/24/19</TD><TD>VXX</TD><TD>S</TD><TD>1</TD><TD>26.6</TD><TD>26</TD><TD> $ (0.60)</TD><TD> $ 0.60</TD><TD>2.3%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>66/24/19</TD><TD>XOP</TD><TD>S</TD><TD>1</TD><TD>26</TD><TD>27.25</TD><TD> $ 1.25</TD><TD> $ 1.25</TD><TD>4.81%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Open </TD><TD>Trades</TD><TD> </TD><TD> </TD><TD>614.86</TD><TD>614.27</TD><TD> </TD><TD> $ 61.00</TD><TD>9.92%</TD><TD> $ - </TD><TD>#DIV/0!</TD><TD> </TD></TR><TR><TD>Stocks</TD><TD>6</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Losers</TD><TD>#NAME?</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Open P&L</TD><TD> $ 61.00</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Winners</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>$ winners</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Open Trade</TD><TD>Avg ROI</TD><TD>2.35%</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>$ Losers</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Avg win</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Closed P&L</TD><TD> $ - </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Avg lose</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Profit Factor</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Return On Risk</TD><TD>=</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Weekly Close</TD><TD>Open & Closed</TD><TD>Equity Change</TD><TD>Weekly Acct</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>Date</TD><TD>P/L</TD><TD>Plus/Minus</TD><TD>Gain/Loss %</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>Sun, 30 Jun 2019 00:11:09 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730014#M33738Jim-52019-06-30T00:11:09ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730021#M33739
<P><LI-USER uid="521"></LI-USER> </P><P>I'm trying =COUNTIF(H5,<0,"") for the losers but it's not working. BTW - is there a SUMIF () for the totals ?</P><P> </P><P> </P>Sun, 30 Jun 2019 00:47:17 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730021#M33739Jim-52019-06-30T00:47:17ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730071#M33742
<P><LI-USER uid="368751"></LI-USER> </P><P>The COUTIF() function only has 2 parameters: <STRONG>range</STRONG> and <STRONG>criteria</STRONG></P><P>The formula will work is you select the collection of cells containing winners as losers, making that your range (H5:H10) and your criteria to "L" </P>Sun, 30 Jun 2019 05:50:14 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730071#M33742Excel_World_Champion2019-06-30T05:50:14ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730251#M33755
<P><LI-USER uid="368751"></LI-USER> </P>
<P> </P>
<P>We may only make a guess where is what in your data. If H5:H10 is the range with numbers in dollars, when number of losers</P>
<PRE>=COUNTIF(H5:H10,"<0")</PRE>
<P>and for winners</P>
<PRE>=COUNTIF(H5:H10,">0")</PRE>Sun, 30 Jun 2019 17:59:21 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730251#M33755Sergei Baklan2019-06-30T17:59:21ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730539#M33765
<P><LI-USER uid="521"></LI-USER> </P><P>Thanks so much Sergei, the formulas worked perfect.</P><P>In order to total the numbers for losers and winners, is there an IF statement I use, perhaps =SUMIF () ?</P>Mon, 01 Jul 2019 01:39:22 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730539#M33765Jim-52019-07-01T01:39:22ZRe: Counting in IF stmt
https://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730927#M33773
<P><LI-USER uid="368751"></LI-USER> </P>
<P>You may count all not empty cells in this range</P>
<PRE>=COUNTA(H5:H10)</PRE>
<P> </P>Mon, 01 Jul 2019 08:35:15 GMThttps://techcommunity.microsoft.com/t5/excel/counting-in-if-stmt/m-p/730927#M33773Sergei Baklan2019-07-01T08:35:15Z