Sumproducts in a date range

[Ovyd]

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!

 

[Ardus Petus]

Hi Ovyd,

=SUMPRODUCT(activity!c2:c1000,--(activity!A2:A1000>=--"31/3/2005"),--(activity!A2:A1000<--"01/4/2006"))

HTH

 

[Ovyd]

I have been banging my head about that one for so long now. You are a
lifesaver. Many thanks!

Cheers!
Ovyd

 

[Ardus Petus]

Glad I could help!
(I discovered SUMPRODUCT via forums a couple of months ago)

Cheers
--
AP

"Ovyd" <[email protected]> a écrit dans le message de (e-mail address removed)...
I have been banging my head about that one for so long now. You are a
lifesaver. Many thanks!

Cheers!
Ovyd