Summing data between two date criteria

[Anna Wood]

=SUMPRODUCT(--(I6:I250<DATE(2009,1,1)),--(ISNUMBER(I6:I250)),J6:J250)

I am using this formula to calcuate the number of square footage with an
expiration date less than 1/1/09 (some of the cells contain the value
"month-to-month").

I now need to write this same formula for expiration dates between 1/1/09
and 6/30/09.

How do I get multiple date parameters - I keep getting errors trying to use
the AND function.

 

[Teethless mama]

=SUMPRODUCT(--(I6:I250>=DATE(2009,1,1)),--(I6:I250<=DATE(2009,6,30)),J6:J250)

 

[T. Valko]

Use cells to hold your date boundaries:

A1 = lower date boundary
B1 = upper date boundary

Then:

=SUMPRODUCT(--(I6:I250>=A1),--(I6:I250<=B1),--(ISNUMBER(I6:I250)),J6:J250)

You may not need the --(ISNUMBER(I6:I250)) test. Try removing and see if it
works.

 

[Shane Devenshire]

Hi,

ISNUMBER wasn't needed in your original formula, nor in the suggests you
recieved.

Your original could be
=SUMPRODUCT(--(I6:I250<DATE(2009,6,1)),J6:J250)

 

[Anna Wood]

If I remove the ISNUMBER, the formula results in 278,025. With the ISNUMBER,
the formula results in 10,000. 10,000 is the correct number.

Thank you for your help in adding the second date to the formula.

 

[T. Valko]

You're welcome. Thanks for the feedback!