summation formula help

[squalls]

Hi, i need some help in determining the best way to sum a set of numbers. Basically, i would like to sum by month and by positive or negative. So, for example i have the below spreadsheet:

Col A Col B
5/21 97
5/30 80
6/4 -92
6/10 50
6/12 75

Is there a formula I could use to sum all positive numbers for the month of June? Also, would it be possible to do that without using "dates after 5/31/2011 and before 7/1/2011"? Basically i am looking to keep this formula referenced to the first day of each month and as that date goes from 5/1 to 6/1, i would like the formula to update as well.

thanks so much for your help

 

[Aladin Akyurek]

Hi, i need some help in determining the best way to sum a set of numbers. Basically, i would like to sum by month and by positive or negative. So, for example i have the below spreadsheet:

Col A Col B
5/21 97
5/30 80
6/4 -92
6/10 50
6/12 75

Is there a formula I could use to sum all positive numbers for the month of June? Also, would it be possible to do that without using "dates after 5/31/2011 and before 7/1/2011"? Basically i am looking to keep this formula referenced to the first day of each month and as that date goes from 5/1 to 6/1, i would like the formula to update as well.

thanks so much for your help

E2: 1-Jun-11

On Excel 2007 or later:

=SUMIFS($B$2:$B$6,$B$2:$B$6,">0",$A$2:$A$6,">="&E2,
    $A$2:$A$6,"<="&EOMONTH(E2,0))

On all versions:

=SUMPRODUCT(
    $B$2:$B$6,
    --($B$2:$B$6>0),
    --($A$2:$A$6-DAY($A$2:$A$6)+1=E2))

For negs, change > to <.

 

[squalls]

Thank you so much, that formula worked, now i dont have to have all these additional hidden columns to achieve my desired result.

thanks!

 

[Aladin Akyurek]

Thank you so much, that formula worked, now i dont have to have all these additional hidden columns to achieve my desired result.

thanks!

You are welcome. Thanks for providing feedback.