If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SumIf conditional to date range
I have the following data set:
A B 1 10/7/2008 2 3 10/1/2008 92 4 10/8/2008 59 5 10/17/2008 31 6 10/22/2008 42 7 10/29/2008 28 8 9 Total = 92 Where A1 is the current date, I want to sum the total of column B for all cells where column B corresponds to a date less than cell A1. This total will be displayed in cell B9. I'm aware of the SUMIF function, but haven't been able to figure out how to sum cells based on date without manually changing the formula. Ideally, for example, if today's date is October 7th, then the formula in cell A1 is =Today(), and the formula in cell B9 is =SUMIF(A3:B7,"A1",B3:B7). However, SUMIF doesn't work that way. Is there an alternative. Please help! Thanks in advance! |
#2
|
|||
|
|||
SumIf conditional to date range
Make it easy:
=SUMPRODUCT(--(A3:A7A1),(B3:B7)) "IlliniDan" wrote: I have the following data set: A B 1 10/7/2008 2 3 10/1/2008 92 4 10/8/2008 59 5 10/17/2008 31 6 10/22/2008 42 7 10/29/2008 28 8 9 Total = 92 Where A1 is the current date, I want to sum the total of column B for all cells where column B corresponds to a date less than cell A1. This total will be displayed in cell B9. I'm aware of the SUMIF function, but haven't been able to figure out how to sum cells based on date without manually changing the formula. Ideally, for example, if today's date is October 7th, then the formula in cell A1 is =Today(), and the formula in cell B9 is =SUMIF(A3:B7,"A1",B3:B7). However, SUMIF doesn't work that way. Is there an alternative. Please help! Thanks in advance! |
#3
|
|||
|
|||
SumIf conditional to date range
=SUMPRODUCT((A3:A7A1)*(B3:B7))
"IlliniDan" wrote in message ... I have the following data set: A B 1 10/7/2008 2 3 10/1/2008 92 4 10/8/2008 59 5 10/17/2008 31 6 10/22/2008 42 7 10/29/2008 28 8 9 Total = 92 Where A1 is the current date, I want to sum the total of column B for all cells where column B corresponds to a date less than cell A1. This total will be displayed in cell B9. I'm aware of the SUMIF function, but haven't been able to figure out how to sum cells based on date without manually changing the formula. Ideally, for example, if today's date is October 7th, then the formula in cell A1 is =Today(), and the formula in cell B9 is =SUMIF(A3:B7,"A1",B3:B7). However, SUMIF doesn't work that way. Is there an alternative. Please help! Thanks in advance! |
#4
|
|||
|
|||
SumIf conditional to date range
=sumif(a3:a7,""&a1,b3:b7)
or even =sumif(a3:a7,""&today(),b3:b7) (to avoid putting the formula in A1) IlliniDan wrote: I have the following data set: A B 1 10/7/2008 2 3 10/1/2008 92 4 10/8/2008 59 5 10/17/2008 31 6 10/22/2008 42 7 10/29/2008 28 8 9 Total = 92 Where A1 is the current date, I want to sum the total of column B for all cells where column B corresponds to a date less than cell A1. This total will be displayed in cell B9. I'm aware of the SUMIF function, but haven't been able to figure out how to sum cells based on date without manually changing the formula. Ideally, for example, if today's date is October 7th, then the formula in cell A1 is =Today(), and the formula in cell B9 is =SUMIF(A3:B7,"A1",B3:B7). However, SUMIF doesn't work that way. Is there an alternative. Please help! Thanks in advance! -- Dave Peterson |
#5
|
|||
|
|||
SumIf conditional to date range
You don't need the parens around the B3:B7
here is a simplify version: =SUMPRODUCT(--(A3:A7A1),B3:B7) "Sean Timmons" wrote: Make it easy: =SUMPRODUCT(--(A3:A7A1),(B3:B7)) "IlliniDan" wrote: I have the following data set: A B 1 10/7/2008 2 3 10/1/2008 92 4 10/8/2008 59 5 10/17/2008 31 6 10/22/2008 42 7 10/29/2008 28 8 9 Total = 92 Where A1 is the current date, I want to sum the total of column B for all cells where column B corresponds to a date less than cell A1. This total will be displayed in cell B9. I'm aware of the SUMIF function, but haven't been able to figure out how to sum cells based on date without manually changing the formula. Ideally, for example, if today's date is October 7th, then the formula in cell A1 is =Today(), and the formula in cell B9 is =SUMIF(A3:B7,"A1",B3:B7). However, SUMIF doesn't work that way. Is there an alternative. Please help! Thanks in advance! |
#6
|
|||
|
|||
SumIf conditional to date range
Thanks to everyone who responded so quickly, much appreciated! Both the
Sumproduct and SumIf solutions worked. "IlliniDan" wrote: I have the following data set: A B 1 10/7/2008 2 3 10/1/2008 92 4 10/8/2008 59 5 10/17/2008 31 6 10/22/2008 42 7 10/29/2008 28 8 9 Total = 92 Where A1 is the current date, I want to sum the total of column B for all cells where column B corresponds to a date less than cell A1. This total will be displayed in cell B9. I'm aware of the SUMIF function, but haven't been able to figure out how to sum cells based on date without manually changing the formula. Ideally, for example, if today's date is October 7th, then the formula in cell A1 is =Today(), and the formula in cell B9 is =SUMIF(A3:B7,"A1",B3:B7). However, SUMIF doesn't work that way. Is there an alternative. Please help! Thanks in advance! |
Thread Tools | |
Display Modes | |
|
|