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
 
Ad

Advertisements

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")).
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top