Sumproducts in a date range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ovyd - 11 Jul 2006 17:37 GMT

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!

Reply to this Message

Ardus Petus - 11 Jul 2006 17:53 GMT

Hi Ovyd,

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

HTH
--
AP

> Good day,
>
[quoted text clipped - 22 lines]
>
> Many thanks to any who could venture a guess!

Reply to this Message

Ovyd - 11 Jul 2006 18:01 GMT

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

Cheers!
Ovyd

> Hi Ovyd,
>
[quoted text clipped - 30 lines]
> >
> > Many thanks to any who could venture a guess!

Reply to this Message

Ardus Petus - 11 Jul 2006 18:19 GMT

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

Cheers
--
AP

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

Cheers!
Ovyd

Ardus Petus wrote:

> Hi Ovyd,
>
[quoted text clipped - 32 lines]
> >
> > Many thanks to any who could venture a guess!

Reply to this Message

Rate this thread: