Adding amounts based on a conditional date
 

How can I sum a range of amounts, each of which has a respective date
associated with it, based on whether or not that date is less than or equal
to a cutoff date (i.e, add together all the amounts that occured between
10/1/05 and 10/31/05?

Hi

One way, assuming your dates to be in column A and your amounts in column B
=SUMPRODUCT(--($A$1:$A$100=DATE(2005,10,1)),--($A$1:$A$100<=DATE(2005,10,31),B1:B100)

Change ranges to suit, but do ensure they are all of equal size.

Regards

Roger Govier



ruleb wrote:

How can I sum a range of amounts, each of which has a respective date
associated with it, based on whether or not that date is less than or equal
to a cutoff date (i.e, add together all the amounts that occured between
10/1/05 and 10/31/05?

Roger-

Thanks, I see the logic you are using here. However, I am getting an error
message with the formula. I've triple checked everything compared to what
you put in here, but it just isn't working. Is there a paren missing or
anything? Like I said, I understand the logic, but just can't get it
expressed through this formula.

THANKS!

"Roger Govier" wrote:

Hi

One way, assuming your dates to be in column A and your amounts in column B
=SUMPRODUCT(--($A$1:$A$100=DATE(2005,10,1)),--($A$1:$A$100<=DATE(2005,10,31),B1:B100)

Change ranges to suit, but do ensure they are all of equal size.

Regards

Roger Govier



ruleb wrote:

How can I sum a range of amounts, each of which has a respective date
associated with it, based on whether or not that date is less than or equal
to a cutoff date (i.e, add together all the amounts that occured between
10/1/05 and 10/31/05?

Hi

Yes, you are right, I missed out a closing parenthesis.
Try

=SUMPRODUCT(--($A$1:$A$100=DATE(2005,10,1)),--($A$1:$A$100<=DATE(2005,10,31)),B1:B100)


Regards

Roger Govier



ruleb wrote:

Roger-

Thanks, I see the logic you are using here. However, I am getting an error
message with the formula. I've triple checked everything compared to what
you put in here, but it just isn't working. Is there a paren missing or
anything? Like I said, I understand the logic, but just can't get it
expressed through this formula.

THANKS!

"Roger Govier" wrote:



Hi

One way, assuming your dates to be in column A and your amounts in column B
=SUMPRODUCT(--($A$1:$A$100=DATE(2005,10,1)),--($A$1:$A$100<=DATE(2005,10,31),B1:B100)

Change ranges to suit, but do ensure they are all of equal size.

Regards

Roger Govier



ruleb wrote:



How can I sum a range of amounts, each of which has a respective date
associated with it, based on whether or not that date is less than or equal
to a cutoff date (i.e, add together all the amounts that occured between
10/1/05 and 10/31/05?