Sumproduct - Date Range

[Jackrabbit181]

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

 

[T. Valko]

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
D1100 = values to sum

=SUMPRODUCT(--(C1:C100>=A1),--(C1:C100<=B1),D1100)

Or:

=SUMIF(C1:C100,">="&A1,D1100)-SUMIF(C1:C100,">"&B1,D1100)

 

[Rick Rothstein]

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