| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Monthly, Quarterly formulas | Excel Worksheet Functions | |||
| Chart with Quarterly and Monthly Results | Charts and Charting in Excel | |||
| Monthly Data, need quarterly Stdev | Excel Discussion (Misc queries) | |||
| monthly quarterly splitting | Excel Discussion (Misc queries) | |||
| From Monthly to Quarterly Numbers | Excel Discussion (Misc queries) | |||
Linear Mode
