Summing data between two date criteria

  • Thread starter Thread starter Anna Wood
  • Start date Start date
A

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.
 
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.
 
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)
 
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.
 
Back
Top