Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quarterly Formula
Hi!
I'm trying to find a formula to extract a total within each quarter without including the year, thus avoiding on a yearly basis updating the year within the formula. The formula at present is : =SUMPRODUCT(--(D2:D1000="Blaaa"),--(A2:A1000="Blaaa"),--(B2:B1000=DATE(2010,1,1)),--(B2:B1000<=DATE(2010,3,31))) I was given a formula but I can't get it to work for the above. The formula given is: First hit on web search: http://www.exceltip.com/st/Calculati..._Year/932.html quarter based on calendar year (with date in A2) =INT((MONTH(A2)-1)/3)+1 quarter based on FY starting in September, again w/date in A2: =MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1 Thank you. -- Carol |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Quarterly Formula
Hi! No response required, finally figured where I was going wrong.
Thanks. -- Carol "Caroline" wrote: Hi! I'm trying to find a formula to extract a total within each quarter without including the year, thus avoiding on a yearly basis updating the year within the formula. The formula at present is : =SUMPRODUCT(--(D2:D1000="Blaaa"),--(A2:A1000="Blaaa"),--(B2:B1000=DATE(2010,1,1)),--(B2:B1000<=DATE(2010,3,31))) I was given a formula but I can't get it to work for the above. The formula given is: First hit on web search: http://www.exceltip.com/st/Calculati..._Year/932.html quarter based on calendar year (with date in A2) =INT((MONTH(A2)-1)/3)+1 quarter based on FY starting in September, again w/date in A2: =MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1 Thank you. -- Carol |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simplify formula using quarterly investment returns | Excel Worksheet Functions | |||
Quarterly Formula | New Users to Excel | |||
Quarterly inputs | Excel Worksheet Functions | |||
is there a formula for quarterly expenses in Excel? | Excel Worksheet Functions | |||
Formula Help to Calculate Quarterly Results | Excel Discussion (Misc queries) |