monthly & quarterly Summery
 

What is the formula for getting the monthly & quarerly sum for the below?
A2 B2
01/01/2008 43
02/02/2008 32
03/03/2008 43
04/03/2008 54
05/04/2008 5
06/05/2008 32
07/06/2008 75
08/07/2008 3
09/08/2008 23
10/08/2008 64
11/09/2008 76
12/09/2008 43

monthly & quarterly Summery
 

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=1),$B$2:$B$13)

--
__________________________________
HTH

Bob

"Narnimar" wrote in message
...
What is the formula for getting the monthly & quarerly sum for the below?
A2 B2
01/01/2008 43
02/02/2008 32
03/03/2008 43
04/03/2008 54
05/04/2008 5
06/05/2008 32
07/06/2008 75
08/07/2008 3
09/08/2008 23
10/08/2008 64
11/09/2008 76
12/09/2008 43

monthly & quarterly Summery
 

Thanks. I get 1st quarter Sum using this formula. Now my question is -
1. how to get sum for 2nd 3rd and 4th Quarter?
2. how to get sum for each month?

"Bob Phillips" wrote:

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=1),$B$2:$B$13)

--
__________________________________
HTH

Bob

"Narnimar" wrote in message
...
What is the formula for getting the monthly & quarerly sum for the below?
A2 B2
01/01/2008 43
02/02/2008 32
03/03/2008 43
04/03/2008 54
05/04/2008 5
06/05/2008 32
07/06/2008 75
08/07/2008 3
09/08/2008 23
10/08/2008 64
11/09/2008 76
12/09/2008 43

monthly & quarterly Summery
 

"narnimar" wrote in message
...
Thanks. I get 1st quarter Sum using this formula. Now my question is -
1. how to get sum for 2nd 3rd and 4th Quarter?

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=2),$B$2:$B$13)

or better, put the quarter number in a cell, say H1, and use

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=H1),$B$2:$B$13)

etc.


2. how to get sum for each month?

put the month number in a acell and use

=SUMPRODUCT((--($A$2:$A$13<""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

etc.

monthly & quarterly Summery
 

The answer for Question No. 1. is working perfect!
But your formula returns error message for my Question 2. The pop up with
mesage that "the formula you have entered has an error"

"Bob Phillips" wrote:



"narnimar" wrote in message
...
Thanks. I get 1st quarter Sum using this formula. Now my question is -
1. how to get sum for 2nd 3rd and 4th Quarter?

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=2),$B$2:$B$13)

or better, put the quarter number in a cell, say H1, and use

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=H1),$B$2:$B$13)

etc.


2. how to get sum for each month?

put the month number in a acell and use

=SUMPRODUCT((--($A$2:$A$13<""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

etc.

monthly & quarterly Summery
 

One bracket too many

=SUMPRODUCT(--($A$2:$A$13<""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

--
__________________________________
HTH

Bob

"narnimar" wrote in message
...
The answer for Question No. 1. is working perfect!
But your formula returns error message for my Question 2. The pop up with
mesage that "the formula you have entered has an error"

"Bob Phillips" wrote:



"narnimar" wrote in message
...
Thanks. I get 1st quarter Sum using this formula. Now my question is -
1. how to get sum for 2nd 3rd and 4th Quarter?

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=2),$B$2:$B$13)

or better, put the quarter number in a cell, say H1, and use

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=H1),$B$2:$B$13)

etc.


2. how to get sum for each month?

put the month number in a acell and use

=SUMPRODUCT((--($A$2:$A$13<""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

etc.

monthly & quarterly Summery
 

This is Perfect! Thank you very much Mr. Bob.

"Bob Phillips" wrote:

One bracket too many

=SUMPRODUCT(--($A$2:$A$13<""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

--
__________________________________
HTH

Bob

"narnimar" wrote in message
...
The answer for Question No. 1. is working perfect!
But your formula returns error message for my Question 2. The pop up with
mesage that "the formula you have entered has an error"

"Bob Phillips" wrote:



"narnimar" wrote in message
...
Thanks. I get 1st quarter Sum using this formula. Now my question is -
1. how to get sum for 2nd 3rd and 4th Quarter?

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=2),$B$2:$B$13)

or better, put the quarter number in a cell, say H1, and use

=SUMPRODUCT(--(INT((MONTH($A$2:$A$13)+2)/3)=H1),$B$2:$B$13)

etc.


2. how to get sum for each month?

put the month number in a acell and use

=SUMPRODUCT((--($A$2:$A$13<""),--(MONTH($A$2:$A$13)=H1),$B$2:$B$13)

etc.