Quarterly Formula
in [General]
|
From: Caroline on 13 Apr 2010 14:26 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/Calculating_the_Quarter_Number_for_Calendar_and_Fiscal_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
From: Caroline on 13 Apr 2010 16:54 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/Calculating_the_Quarter_Number_for_Calendar_and_Fiscal_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
|
Pages: 1 Prev: xl 2007 how to validate that cell 1 invalid if cell 2 not blank Next: No left click |