Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Column Entries that fall within Date Range Listed In Rows
I'm trying to sum a list of values in a column, where the date
identifed in each row is = Now. As example: 7/10/06 100.00 7/11/06 200.00 7/12/06 150.00 7/13/06 250.00 My formula (in theory) needs to be something like: =if(a1=Now(),sum(b1:b4) If Now = 7/12/06, then I need it to return the sum of 7/12 and 7/13 for a total of 400.00. If Now = 7/13/06, then I need it to return the sum of 7/13 for a total of 250.00. My formual will ultimately be more complicated than this as between each date there are 5 rows of information. The date cell is a merge of these 5 cells, and then each row has values totaling up to 100.00, 200.00 etc., so I'll need to then get these extra values subtracted out. I think I can do the last part, if I can just fugure out how to capture only those values = Now. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Column Entries that fall within Date Range Listed In Rows
Ben wrote:
I'm trying to sum a list of values in a column, where the date identifed in each row is = Now. As example: 7/10/06 100.00 7/11/06 200.00 7/12/06 150.00 7/13/06 250.00 My formula (in theory) needs to be something like: =if(a1=Now(),sum(b1:b4) If Now = 7/12/06, then I need it to return the sum of 7/12 and 7/13 for a total of 400.00. If Now = 7/13/06, then I need it to return the sum of 7/13 for a total of 250.00. My formual will ultimately be more complicated than this as between each date there are 5 rows of information. The date cell is a merge of these 5 cells, and then each row has values totaling up to 100.00, 200.00 etc., so I'll need to then get these extra values subtracted out. I think I can do the last part, if I can just fugure out how to capture only those values = Now. Any suggestions? Hi Ben, You can use the SUMPRODUCT function: =SUMPRODUCT((A1:A4)=TODAY())*(B1:B4)) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Column Entries that fall within Date Range Listed In Rows
Try this:
=SUMIF(A1:A4,"="&TODAY(),B1:B4) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Ben" wrote in message oups.com... I'm trying to sum a list of values in a column, where the date identifed in each row is = Now. As example: 7/10/06 100.00 7/11/06 200.00 7/12/06 150.00 7/13/06 250.00 My formula (in theory) needs to be something like: =if(a1=Now(),sum(b1:b4) If Now = 7/12/06, then I need it to return the sum of 7/12 and 7/13 for a total of 400.00. If Now = 7/13/06, then I need it to return the sum of 7/13 for a total of 250.00. My formual will ultimately be more complicated than this as between each date there are 5 rows of information. The date cell is a merge of these 5 cells, and then each row has values totaling up to 100.00, 200.00 etc., so I'll need to then get these extra values subtracted out. I think I can do the last part, if I can just fugure out how to capture only those values = Now. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Column Entries that fall within Date Range Listed In Rows
Ben, Try, =SUMPRODUCT((A1:A4=TODAY())*(B1:B4)) Use TODAY instead of NOW unless you need to evaluate the time and not just the date. NOW() will return date and time so in your sample data the above formula will exclude the value for 7/10/2006 from the sum because the default time when you enter a date is 12:00 AM which is not greater than or equal to NOW(). HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=559945 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Column Entries that fall within Date Range Listed In Rows
Thanks everyone for your help. Using Today instead of Now helped, and
both SumIF and SumProduct worked. My problem was in the more complex portion of my post, where I said each date had 5 rows and I merged the 5 cells together for the date value...then tryed to read and sum the second column. I think Excel needed a 1 to 1 relationship between date cell and my second column in order to sum the values, so removing the merge corrected the problem. Once I did this and put the holding date in the 5th row at each occurence, the calculations worked. Thanks again for your help!!!! SteveG wrote: Ben, Try, =SUMPRODUCT((A1:A4=TODAY())*(B1:B4)) Use TODAY instead of NOW unless you need to evaluate the time and not just the date. NOW() will return date and time so in your sample data the above formula will exclude the value for 7/10/2006 from the sum because the default time when you enter a date is 12:00 AM which is not greater than or equal to NOW(). HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=559945 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |