|
Sum based on date range
Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows
represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put:
=SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
Hi!
One way: =SUMPRODUCT((D3:D7="category 3")*(E2:AI2=--"3/10/2006")*(E2:AI2<=--"3/15/2006"),E3:AI7) Biff "Michael" wrote in message ... Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
Thanks, I was able to get that to work for one sheet then hit another snag...
There is a worksheet for each month of the year and I am trying to sum on a 13th sheet. I am having trouble creating ranges in the formula that span the 12 monthly worksheets. BTW, the assumption below is correct but it is on sheet 13. Should I or could I used named ranges to accomplish the multi-sheet dilemma? "Toppers" wrote: Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put: =SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
Michael,
How do we handle the date ranges for each sheet? It appears we will need a fomula for each month as the dates will (obviously) be diferent. "Michael" wrote: Thanks, I was able to get that to work for one sheet then hit another snag... There is a worksheet for each month of the year and I am trying to sum on a 13th sheet. I am having trouble creating ranges in the formula that span the 12 monthly worksheets. BTW, the assumption below is correct but it is on sheet 13. Should I or could I used named ranges to accomplish the multi-sheet dilemma? "Toppers" wrote: Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put: =SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
There are 12 sheets, one for each month. Row one has the days of the month,
column A contains the categories and to the right of the categories and under each day of the month values are entered throughout the month for each day. Sheet one row one contains the dates 1/1/06 through 1/31/06 Sheet two row one contains the dates 2/1/06 through 2/28/06 etc. through sheet twelve. The sheets are named jan, feb, mar, etc. Sheet 13 would summarize the category values based on a from and to date range which may cross over more than one sheet but not three. "Toppers" wrote: Michael, How do we handle the date ranges for each sheet? It appears we will need a fomula for each month as the dates will (obviously) be diferent. "Michael" wrote: Thanks, I was able to get that to work for one sheet then hit another snag... There is a worksheet for each month of the year and I am trying to sum on a 13th sheet. I am having trouble creating ranges in the formula that span the 12 monthly worksheets. BTW, the assumption below is correct but it is on sheet 13. Should I or could I used named ranges to accomplish the multi-sheet dilemma? "Toppers" wrote: Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put: =SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
As an example, for dates 13th Feb to 20th March inclusive this would provide
a (relatively) simple solution: =SUMPRODUCT(--(Feb!$B$1:$W$1=DATE(2006,2,13)),--(Feb1!$B$1:$W$1<=DATE(2006,2,28)),--(Feb!B2:W2)) + =SUMPRODUCT(--(Mar!$B$1:$W$1=DATE(2006,3,1)),--(Mar!$B$1:$W$1<=DATE(2006,3,20)),--(Mar!B2:W2)) There may be a more "sophisticated" formula but I wouldn't know how to construct it. Is this exceptable? "Michael" wrote: There are 12 sheets, one for each month. Row one has the days of the month, column A contains the categories and to the right of the categories and under each day of the month values are entered throughout the month for each day. Sheet one row one contains the dates 1/1/06 through 1/31/06 Sheet two row one contains the dates 2/1/06 through 2/28/06 etc. through sheet twelve. The sheets are named jan, feb, mar, etc. Sheet 13 would summarize the category values based on a from and to date range which may cross over more than one sheet but not three. "Toppers" wrote: Michael, How do we handle the date ranges for each sheet? It appears we will need a fomula for each month as the dates will (obviously) be diferent. "Michael" wrote: Thanks, I was able to get that to work for one sheet then hit another snag... There is a worksheet for each month of the year and I am trying to sum on a 13th sheet. I am having trouble creating ranges in the formula that span the 12 monthly worksheets. BTW, the assumption below is correct but it is on sheet 13. Should I or could I used named ranges to accomplish the multi-sheet dilemma? "Toppers" wrote: Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put: =SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
I will set it up that way and test it, thanks!
"Toppers" wrote: As an example, for dates 13th Feb to 20th March inclusive this would provide a (relatively) simple solution: =SUMPRODUCT(--(Feb!$B$1:$W$1=DATE(2006,2,13)),--(Feb1!$B$1:$W$1<=DATE(2006,2,28)),--(Feb!B2:W2)) + =SUMPRODUCT(--(Mar!$B$1:$W$1=DATE(2006,3,1)),--(Mar!$B$1:$W$1<=DATE(2006,3,20)),--(Mar!B2:W2)) There may be a more "sophisticated" formula but I wouldn't know how to construct it. Is this exceptable? "Michael" wrote: There are 12 sheets, one for each month. Row one has the days of the month, column A contains the categories and to the right of the categories and under each day of the month values are entered throughout the month for each day. Sheet one row one contains the dates 1/1/06 through 1/31/06 Sheet two row one contains the dates 2/1/06 through 2/28/06 etc. through sheet twelve. The sheets are named jan, feb, mar, etc. Sheet 13 would summarize the category values based on a from and to date range which may cross over more than one sheet but not three. "Toppers" wrote: Michael, How do we handle the date ranges for each sheet? It appears we will need a fomula for each month as the dates will (obviously) be diferent. "Michael" wrote: Thanks, I was able to get that to work for one sheet then hit another snag... There is a worksheet for each month of the year and I am trying to sum on a 13th sheet. I am having trouble creating ranges in the formula that span the 12 monthly worksheets. BTW, the assumption below is correct but it is on sheet 13. Should I or could I used named ranges to accomplish the multi-sheet dilemma? "Toppers" wrote: Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put: =SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
Sum based on date range
With your sheet named Jan - Dec the dates in question lower date in A1 and
upper date in B1 on the summary sheet =SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar"; "Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"D ec"}&"'!1:1"),"="&A1,INDIRECT("'"&{"Jan";"Feb";"M ar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov ";"Dec"}&"'!3:3")))-SUMPRODUCT(SUMIF(INDIRECT("'"&{"Jan";"Feb";"Mar";" Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"De c"}&"'!1:1"),""&B1,INDIRECT("'"&{"Jan";"Feb";"Mar ";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov"; "Dec"}&"'!3:3"))) if the sheet names are in the range J1:J12 =SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),"= "&A1,INDIRECT("'"&J1:J12&"'!3:3")))-SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),""& B1,INDIRECT("'"&J1:J12&"'!3:3"))) both formulas sum row 3, change the 3:3 to another row for another category, you could let a formula do that as well, assume you have a list of categories in the summary sheet called MyList and you put the category in C1 =SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),"= "&A1,INDIRECT("'"&J1:J12&"'!"&MATCH(C1,MyList,0)&" :"&MATCH(C1,MyList,0))))-SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!1:1"),""& B1,INDIRECT("'"&J1:J12&"'!"&MATCH(C1,MyList,0)&":" &MATCH(C1,MyList,0)))) - Regards, Peo Sjoblom "Michael" wrote in message ... There are 12 sheets, one for each month. Row one has the days of the month, column A contains the categories and to the right of the categories and under each day of the month values are entered throughout the month for each day. Sheet one row one contains the dates 1/1/06 through 1/31/06 Sheet two row one contains the dates 2/1/06 through 2/28/06 etc. through sheet twelve. The sheets are named jan, feb, mar, etc. Sheet 13 would summarize the category values based on a from and to date range which may cross over more than one sheet but not three. "Toppers" wrote: Michael, How do we handle the date ranges for each sheet? It appears we will need a fomula for each month as the dates will (obviously) be diferent. "Michael" wrote: Thanks, I was able to get that to work for one sheet then hit another snag... There is a worksheet for each month of the year and I am trying to sum on a 13th sheet. I am having trouble creating ranges in the formula that span the 12 monthly worksheets. BTW, the assumption below is correct but it is on sheet 13. Should I or could I used named ranges to accomplish the multi-sheet dilemma? "Toppers" wrote: Assuming Sheet2 has Category1, Category2 etc in column A, then in column B put: =SUMPRODUCT(--(Sheet1!$B$1:$W$1=DATE(2006,3,3)),--(Sheet1!$B$1:$W$1<=DATE(2006,3,20)),--(Sheet1!B2:W2)) for period 3rd to 20th march 2006 inclusive <b1:w1 contain dates <b2:w2 contain amounts (for category 1) Copy down as required HTH "Michael" wrote: Columns represent days of the month (E2 is day 1, F2 is day 2, etc), rows represent dollar amounts (E3 is $ amt for day one, F3 is $ amt fo day 2, etc.) How can I sum the rows based on a begin and end date? Example: Begin date is March 10, end date is March 15, I need row 3 summed for the range of columns that are within the from and to date range. 3/1 3/2 3/3 3/4 etc. category 1 50.00 5.00 2.00 3.50 category 2 1.00 10.00 .75 .25 I need the formula result to show on another sheet in one column for each row, I suppose I could just copy the formula down the column. Thanks! |
| All times are GMT +1. The time now is 02:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com