getting data from monthly quarter

**andyx181x** · 09-27-2010, 04:51 PM

Hi there folks, could use a helping hand here if possible. As of right now i have a column that has dates (mm/dd/yyyy) from lowest to largest. I already set up a formula to tell me how many times a job occurs within a year (2001, 2002, 2003 etc).

=SUMPRODUCT(--(YEAR(G2:G174)=2001))

What i need now is instead of giving me all the jobs within one year, i need a formula to give me the jobs within a quater system. So for 2001 how many jobs were between months 01-03, 04-06, 07-09, 10-12. I know i will need at least four rows per year, i really appreciate the help.

**marc5354** · 09-27-2010, 04:53 PM

Please expand your demand... if possible please attached your workbook.

Marc

**andyx181x** · 09-27-2010, 05:13 PM

Originally Posted by marc5354

Please expand your demand... if possible please attached your workbook.

Marc

i can't submit my workbook cause its for work purposes, in essence look at the formula i derived and expand it to instead of just viewing all of that one year, i want specific months broken apart. I want how many jobs were in 2001 between the months of 01-03

**andyx181x** · 09-28-2010, 10:52 AM

Ok i got a new formula that is telling me how many months within a year occur, now i need it to some how tell me more then one month

=SUMPRODUCT(--(MONTH(G2:G174)=2)*(--(YEAR(G2:G174)=2009)))

**daddylonglegs** · 09-28-2010, 11:46 AM

That's a slightly confused syntax, normally either

=SUMPRODUCT(--(MONTH(G2:G174)=2),--(YEAR(G2:G174)=2009))

or

=SUMPRODUCT((MONTH(G2:G174)=2)*(YEAR(G2:G174)=2009))

You could expand the second one to count multiple months like this

=SUMPRODUCT((MONTH(G2:G174)={1,2,3})*(YEAR(G2:G174)=2009))

or just count between the start and end dates

=SUMPRODUCT((G2:G174>=DATE(2009,1,1))*(G2:G174<=DATE(2009,3,31))

**andyx181x** · 09-28-2010, 12:34 PM

Originally Posted by daddylonglegs

That's a slightly confused syntax, normally either

=SUMPRODUCT(--(MONTH(G2:G174)=2),--(YEAR(G2:G174)=2009))

or

=SUMPRODUCT((MONTH(G2:G174)=2)*(YEAR(G2:G174)=2009))

You could expand the second one to count multiple months like this

=SUMPRODUCT((MONTH(G2:G174)={1,2,3})*(YEAR(G2:G174)=2009))

or just count between the start and end dates

=SUMPRODUCT((G2:G174>=DATE(2009,1,1))*(G2:G174<=DATE(2009,3,31))

Thanks thats a lot simplier then just repeating the month command. I need one more favor please. The second portion is i have an extra column that next to the row of the date it either says completed or unfullfilled. I need a formula that will count how many times the word unfilled is found within the three month range and single year.