Previous Thread

7/11/2006 9:37:37 AM    Sumproducts in a date range
Good day, 
 
I was wondering if any one could help me with a problem. 
 
My column A contains dates in a dd-mmm-yy format.  Column C contains a 
 
number of events that took place. I am trying to count the number of 
 
events in a fiscal year. 
 
A...................C 
 
01-Jan-05..........1 
 
02-Feb-05.........1 
 
03-Mar-06.........1 
 
11-Apr-06.........1 
 
09-May-06.........1 
 
11-Jun-06..........1 
 
=SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006)) 
 
Will count the events in 2006. 
 
=SUMPRODUCT(--(activity!C2:C1000),--(YEAR(activity!A2:A1000)=2006),--(MONTH(activity!A2:A1000)=7)) 
 
Will count the events in July 2006 
 
But I cannot figure out how to get it to count dates in a range - a 
 
fiscal year that runs from March 31, 2005 to April 1st, 2006. 
 
Many thanks to any who could venture a guess!



7/11/2006 10:01:50 AM    Re: Sumproducts in a date range
I have been banging my head about that one for so long now.  You are a 
 
lifesaver.  Many thanks! 
 
Cheers! 
 
Ovyd 
 
Ardus Petus wrote: 
 
(activity!A2:A1000<--"01/4/2006")) 
 
--(MONTH(activity!A2:A1000)=3D7))

7/11/2006 6:53:01 PM    Re: Sumproducts in a date range
Hi Ovyd, 
 
=SUMPRODUCT(activity!c2:c1000,--(activity!A2:A1000>=--"31/3/2005"),--(activity!A2:A1000<--"01/4/2006")) 
 
HTH 
 
-- 
 
AP 
 
"Ovyd" <graeme_bowles@yahoo.ca> a écrit dans le message de news: 
 
1152635857.062106.83410@h48g2000cwc.googlegroups.com...

7/11/2006 7:19:05 PM    Re: Sumproducts in a date range
Glad I could help! 
 
(I discovered SUMPRODUCT via forums a couple of months ago) 
 
Cheers 
 
-- 
 
AP 
 
"Ovyd" <graeme_bowles@yahoo.ca> a écrit dans le message de news: 
 
1152637310.414741.265080@35g2000cwc.googlegroups.com... 
 
I have been banging my head about that one for so long now.  You are a 
 
lifesaver.  Many thanks! 
 
Cheers! 
 
Ovyd 
 
Ardus Petus wrote: