Summing data between two date criteria
 

=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.

Summing data between two date criteria
 

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


"Anna Wood" wrote:

=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.

Summing data between two date criteria
 

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.

--
Biff
Microsoft Excel MVP


"Anna Wood" wrote:

=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.

Summing data between two date criteria
 

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 this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Anna Wood" wrote:

=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.

Summing data between two date criteria
 

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Anna Wood" wrote:

=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.

Summing data between two date criteria
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Anna Wood" wrote:

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Anna Wood" wrote:

=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.