Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with calculation
On sheet2 I have a list of dates where A1 is 01 Jan 2004, A2 is 01 Feb
2004 etc through to 01 Oct 2005, against each date is a column of figures. So A1 is the date and B1 is the volume. On sheet1 I want to have a formula that will calculate the total volume between 2 dates selected by the user, where D7 is the earliest date and D9 is the latest date. So after they have input the dates the volumes between those dates will be totaled and shown in E10.(They will only be able to input dates for the 1st of the month - so as to match the data held on sheet2.)As you appreciate there will be more than 12 months data held. What is the best approach to this problem. |
#2
|
|||
|
|||
Help with calculation
=SUM(INDIRECT("B"&MATCH(D7,A1:A22)):INDIRECT("B"&M ATCH(D9,A1:A22)))
will work if your dates start on row one. Meaning A1=1/1/2004. If you insert column heading you will need to change it like so. =SUM(INDIRECT("B"&1+MATCH(D7,A2:A23)):INDIRECT("B" &1+MATCH(D9,A2:A23))) MATCH function returns the relative position of an item in a list, even if they are out of order. The list goes to A22 because you said it goes to 10/1/2005. If you want it to grow as you increase the number of dates use... =SUM(INDIRECT("B"&MATCH(D7,A:A)):INDIRECT("B"&MATC H(D9,A:A))) You cannot put anything else in column A if you do this though. The reason this works, is because it does not count blanks in the list. hope this helps. "Box666" wrote: On sheet2 I have a list of dates where A1 is 01 Jan 2004, A2 is 01 Feb 2004 etc through to 01 Oct 2005, against each date is a column of figures. So A1 is the date and B1 is the volume. On sheet1 I want to have a formula that will calculate the total volume between 2 dates selected by the user, where D7 is the earliest date and D9 is the latest date. So after they have input the dates the volumes between those dates will be totaled and shown in E10.(They will only be able to input dates for the 1st of the month - so as to match the data held on sheet2.)As you appreciate there will be more than 12 months data held. What is the best approach to this problem. |
#3
|
|||
|
|||
Help with calculation
Try this in E10 of Sheet1:
=SUMPRODUCT((Sheet2!A1:A22=D7)*(Sheet2!A1:A22<=D9 )*Sheet2!B1:B22) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Box666" wrote in message oups.com... On sheet2 I have a list of dates where A1 is 01 Jan 2004, A2 is 01 Feb 2004 etc through to 01 Oct 2005, against each date is a column of figures. So A1 is the date and B1 is the volume. On sheet1 I want to have a formula that will calculate the total volume between 2 dates selected by the user, where D7 is the earliest date and D9 is the latest date. So after they have input the dates the volumes between those dates will be totaled and shown in E10.(They will only be able to input dates for the 1st of the month - so as to match the data held on sheet2.)As you appreciate there will be more than 12 months data held. What is the best approach to this problem. |
#4
|
|||
|
|||
Help with calculation
=SUMIF(A:A,"="&D7,B:B)-SUMIF(A:A,""&D9,B:B)
is another way you can do it. A lot simpler, but not as fun. :) "Sloth" wrote: =SUM(INDIRECT("B"&MATCH(D7,A1:A22)):INDIRECT("B"&M ATCH(D9,A1:A22))) will work if your dates start on row one. Meaning A1=1/1/2004. If you insert column heading you will need to change it like so. =SUM(INDIRECT("B"&1+MATCH(D7,A2:A23)):INDIRECT("B" &1+MATCH(D9,A2:A23))) MATCH function returns the relative position of an item in a list, even if they are out of order. The list goes to A22 because you said it goes to 10/1/2005. If you want it to grow as you increase the number of dates use... =SUM(INDIRECT("B"&MATCH(D7,A:A)):INDIRECT("B"&MATC H(D9,A:A))) You cannot put anything else in column A if you do this though. The reason this works, is because it does not count blanks in the list. hope this helps. "Box666" wrote: On sheet2 I have a list of dates where A1 is 01 Jan 2004, A2 is 01 Feb 2004 etc through to 01 Oct 2005, against each date is a column of figures. So A1 is the date and B1 is the volume. On sheet1 I want to have a formula that will calculate the total volume between 2 dates selected by the user, where D7 is the earliest date and D9 is the latest date. So after they have input the dates the volumes between those dates will be totaled and shown in E10.(They will only be able to input dates for the 1st of the month - so as to match the data held on sheet2.)As you appreciate there will be more than 12 months data held. What is the best approach to this problem. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Equity buildup calculation | Excel Worksheet Functions | |||
Calculation Setting in Excel | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions |