Excel Tip #6 - How to Count or Sum All the Positive/Negative Numbers in a Row/Column

Excel Formula - Count or Sum All Positive or Negative Numbers in a Row or Column


As mentioned in Pro Stock Trading Tips #1, it is essential for the trader to let gains run and cut losses.

One way of tracking whether or not you are doing this is by actually looking at your trading results. If you track your results in excel, there are simple formulas you can employ to count the number of winners or losers you have and how much the total of your winners or losers is.

Counting number of positive or negative numbers use


a) Number of all positive numbers

=COUNTIF(D5:D20,">0")

b) Number of negative numbers

=COUNTIF(D5:D20,"<0")

Replace D5:D20 with your actual data set.


Summing all the positive or negative numbers separately use:


a) Sum of all positive numbers

=SUMIF(D5:D20,">0")

b) Sum of negative numbers

=SUMIF(D5:D20,"<0")

Again, replace D5:D20 with your actual data set. 


Average all the positive or negative numbers use:


a) Average of all positive numbers

=AVERAGEIF(D5:D20,">0")

b) Average of all negative numbers 

=AVERAGEIF(D5:D20,"<0")

For examples of this see Pro Stock Trading Tips #1 where an excel screenshot has been included. Be sure to replace the D5:D20 to your actual data set.

Labels: ,