# Calculating the Count of Monthly Gains/Losses

S

#### shriil

I have this excel sheet where I keep track of my daily stock trading
gains or losses. It looks like as given below

A B C
Date Gain/Loss in Points Gain/Loss
04-Jul-10 52 G
07-Jul-10 156 G
15-Jul-10 -34 L
01-Aug-10 25 G
12-Aug-10 46 G
18-Aug-10 21 G
8-Sep-10 -22 L
14-Sep-10 -9 L
..... ...

I would like to analyse the above data and find out (a) Monthly
Sumtotal Gain (b) Monthly Sumtotal Loss (c) No of Gains (d) No of
Losses in Columns D,,,E..
Where
D
Mth-Yr Total Gain Tot Loss No.of Gains....No. of Losses
Jul-10
Aug-10
Sep-10
Oct-10
.......

For (a) and (b) .. I would be using the Sumproduct function as
=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))*(YEAR(\$A\$2:\$A
\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G")*(\$B\$2:\$B\$3098))..

for Loss : ...... (\$C2:\$C3098="L")*.....

However I cant figure out how to calculate the No. of Gains /Losses on
a monthly basis.

Would request help on this issue and for any easier function for
calculating the sum of gains and losses

Thks

Shriil

J

#### joeu2004

I would like to analyse the above data and find out (a) Monthly
Sumtotal Gain (b) Monthly Sumtotal Loss (c) No of Gains (d) No of
Losses in Columns D,,,E [...]
For (a) and (b) .. I would be using the Sumproduct function as
=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))*(YEAR(\$A\$2:\$A
\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G")*(\$B\$2:\$B\$3098))..
for Loss : ...... (\$C2:\$C3098="L")*.....

However I cant figure out how to calculate the No. of Gains /Losses on
a monthly basis.

Simply:

=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))*(YEAR(\$A\$2:\$A
\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G"))

J

#### joeu2004

Sorry, my previous posting got mangled due to copy-and-paste errors
(mine).
Let's try again....

I would like to analyse the above data and find out (a) Monthly
Sumtotal Gain (b) Monthly Sumtotal Loss (c) No of Gains (d) No of
Losses in Columns D,,,E [...]
For (a) and (b) .. I would be using the Sumproduct function as
=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))*(YEAR(\$A\$2:\$A
\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G")*(\$B\$2:\$B\$3098))..
for Loss : ...... (\$C2:\$C3098="L")*.....
However I cant figure out how to calculate the No. of Gains /Losses on
a monthly basis.
Shriil

Simply:

=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))
*(YEAR(\$A\$2:\$A\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G")).

S

#### shriil

Sorry, my previous posting got mangled due to copy-and-paste errors
(mine).
Let's try again....

I would like to analyse the above data and find out (a) Monthly
Sumtotal Gain (b) Monthly Sumtotal Loss (c) No of Gains (d) No of
Losses in Columns D,,,E [...]
For (a) and (b) .. I would be using the Sumproduct function as
=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))*(YEAR(\$A\$2:\$A
\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G")*(\$B\$2:\$B\$3098))..
for Loss : ...... (\$C2:\$C3098="L")*.....
However I cant figure out how to calculate the No. of Gains /Losses on
a monthly basis.
Shriil

Simply:

=SUMPRODUCT((MONTH(A\$2:\$A\$3098)=MONTH(\$D2))
*(YEAR(\$A\$2:\$A\$3098)=YEAR(\$D2))*(\$C2:\$C3098="G")).

Thks a lot. Was quite close.. but u showed the final way..!

shriil