Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Add Dates in a formula?
I'm trying to get the sum of dollars spent in each week of the year. The range is compiled of invoice data, with non consecutive dates. Column A contains dates, and column G shows the corresponding dollar amounts. I want to return the total dollars spent for the first week of the month in cell E20. (I will end up having all the weeks of the year listed with the dollars spent in that week.) Should this be a SUMIF function, and how should dates be entered into the formula? -- Rogue ------------------------------------------------------------------------ Rogue's Profile: http://www.excelforum.com/member.php...o&userid=30246 View this thread: http://www.excelforum.com/showthread...hreadid=499185 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Add Dates in a formula?
On Sun, 8 Jan 2006 12:11:06 -0600, Rogue
wrote: I'm trying to get the sum of dollars spent in each week of the year. The range is compiled of invoice data, with non consecutive dates. Column A contains dates, and column G shows the corresponding dollar amounts. I want to return the total dollars spent for the first week of the month in cell E20. (I will end up having all the weeks of the year listed with the dollars spent in that week.) Should this be a SUMIF function, and how should dates be entered into the formula? You can use SUMIF. Let us say you have your WeekStartDates in Z2:Z54, and your range of Data goes and Dates goes from Row2--Row1000. The general form of the formula would be: =SUMIF(A2:A1000,"="&Z2,G2:G1000) - SUMIF(A2:A1000,""&Z2+7,G2:G1000) Format the result as Currency. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Add Dates in a formula?
One way to approach this is to enter the starting date of the first week in
say H1, And the ending date of the first week in I1. Then, enter the starting and ending dates of the second week in H2 and I2. Now, select *all 4* cells, and drag down to copy, as needed. This gives you a datalist of your weeks, which you can reference in a formula. Enter this formula in J1, and copy down as needed: =SUMPRODUCT(($A$1:$A$100=H1)*($A$1:$A$100<=I1)*$G $1:$G$100) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rogue" wrote in message ... I'm trying to get the sum of dollars spent in each week of the year. The range is compiled of invoice data, with non consecutive dates. Column A contains dates, and column G shows the corresponding dollar amounts. I want to return the total dollars spent for the first week of the month in cell E20. (I will end up having all the weeks of the year listed with the dollars spent in that week.) Should this be a SUMIF function, and how should dates be entered into the formula? -- Rogue ------------------------------------------------------------------------ Rogue's Profile: http://www.excelforum.com/member.php...o&userid=30246 View this thread: http://www.excelforum.com/showthread...hreadid=499185 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How Do I Add Dates in a formula?
Assuming your dates and dollar amounts start in row 2 (you have a header
row), the following formula, placed in cell E20, will return the sum of sales for the first ISO week of the year. Copy the formula to cell E21 to get the total for ISO week 2, and so on. =SUMPRODUCT((1+INT((A$2:A$100-DATE(YEAR(A$2:A$100+4-WEEKDAY(A$2:A$100+6)),1,5)+WEEKDAY(DATE(YEAR(A$2:A $100+4-WEEKDAY(A$2:A$100+6)),1,3)))/7)=(ROW()-19))*G$2:G$100) Note that the ranges A$2:A$100 and G$2:G$100 must match the range of existing data. Any empty or non-numeric cells in those ranges will produce a #NUM! error. Also note that the ranges should only span the current year. Since the formula works with ISO week numbers, expanding to more than one year's worth of data might include multiple years in a specific week. Kevin "Ragdyer" wrote in message ... One way to approach this is to enter the starting date of the first week in say H1, And the ending date of the first week in I1. Then, enter the starting and ending dates of the second week in H2 and I2. Now, select *all 4* cells, and drag down to copy, as needed. This gives you a datalist of your weeks, which you can reference in a formula. Enter this formula in J1, and copy down as needed: =SUMPRODUCT(($A$1:$A$100=H1)*($A$1:$A$100<=I1)*$G $1:$G$100) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Rogue" wrote in message ... I'm trying to get the sum of dollars spent in each week of the year. The range is compiled of invoice data, with non consecutive dates. Column A contains dates, and column G shows the corresponding dollar amounts. I want to return the total dollars spent for the first week of the month in cell E20. (I will end up having all the weeks of the year listed with the dollars spent in that week.) Should this be a SUMIF function, and how should dates be entered into the formula? -- Rogue ------------------------------------------------------------------------ Rogue's Profile: http://www.excelforum.com/member.php...o&userid=30246 View this thread: http://www.excelforum.com/showthread...hreadid=499185 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula including dates | Excel Worksheet Functions | |||
I need a formula to sum column b if column a is between two dates | Excel Discussion (Misc queries) | |||
How do I format dates accessed by a formula | Excel Discussion (Misc queries) |