Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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? |
#2
![]() |
|||
|
|||
![]()
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? |
#3
![]() |
|||
|
|||
![]()
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? |
#4
![]() |
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Conditional Formating Based on Date | Excel Discussion (Misc queries) | |||
52 week average based on date | Excel Discussion (Misc queries) | |||
Conditional Formatting Based on Date | Excel Discussion (Misc queries) | |||
conditional formatting based on column | Excel Discussion (Misc queries) | |||
Conditional Formatting (Date vs Number) | Excel Discussion (Misc queries) |