From: Caroline on
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
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