Sumproduct - Date Range
 

I am using dates as a parameter in a Sumproduct, they are formatted as
mmmm-yy but I am only using the last day of the month.

I want to add up everything within a 12 month period. How can I do this
using 'GREATER THAN' and 'LESS THEN OR EQUAL TO'.

So if the date is 31/03/2009, how can I add up everything within the date
range 30/04/2008 - 31/03/2009?

Currently I am doing it by identifying the row ranges for each period but
want the formula to be more dynamic in case things get added.

Thanks
Rick

Sumproduct - Date Range
 

greater than 1 date and equal to or less than another.

Why don't you use the logic of =start date, <=end date.

Use cells to hold your date boundaries.

A1 = start date
B1 = end date

C1:C100 = dates
D1:D100 = values to sum

=SUMPRODUCT(--(C1:C100=A1),--(C1:C100<=B1),D1:D100)

Or:

=SUMIF(C1:C100,"="&A1,D1:D100)-SUMIF(C1:C100,""&B1,D1:D100)

--
Biff
Microsoft Excel MVP


"Jackrabbit181" wrote in message
...
I am using dates as a parameter in a Sumproduct, they are formatted as
mmmm-yy but I am only using the last day of the month.

I want to add up everything within a 12 month period. How can I do this
using 'GREATER THAN' and 'LESS THEN OR EQUAL TO'.

So if the date is 31/03/2009, how can I add up everything within the date
range 30/04/2008 - 31/03/2009?

Currently I am doing it by identifying the row ranges for each period but
want the formula to be more dynamic in case things get added.

Thanks
Rick

Sumproduct - Date Range
 

I want to add up everything within a 12 month period. How can I do this
using 'GREATER THAN' and 'LESS THEN OR EQUAL TO'.

So if the date is 31/03/2009, how can I add up everything within the date
range 30/04/2008 - 31/03/2009?

First off, the date range you show April 30, 2008 to March 31, 2009 is not a
12-month period; rather, it is only 11 months. Did you perhaps mean
01/04/2008 as the start date?

Anyway, assuming your dates are in Column A and the values you want to add
up are in Column B and the date we are using as the key date to calculate
from is in C1...

For the 11-month period your post asked for
----------------------------------------------------------------
=SUMPRODUCT((A1:A1000DATE(YEAR(C1)-1,MONTH(C1)+2,0))*(A1:A1000<=C1)*B1:B1000)

For a full 12-month period
----------------------------------------------------------------
=SUMPRODUCT((A1:A1000DATE(YEAR(C1)-1,MONTH(C1)+1,0))*(A1:A1000<=C1)*B1:B1000)

--
Rick (MVP - Excel)


"Jackrabbit181" wrote in message
...
I am using dates as a parameter in a Sumproduct, they are formatted as
mmmm-yy but I am only using the last day of the month.

I want to add up everything within a 12 month period. How can I do this
using 'GREATER THAN' and 'LESS THEN OR EQUAL TO'.

So if the date is 31/03/2009, how can I add up everything within the date
range 30/04/2008 - 31/03/2009?

Currently I am doing it by identifying the row ranges for each period but
want the formula to be more dynamic in case things get added.

Thanks
Rick