Help with calculation

[Box666]

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.

 

[Sloth]

=SUM(INDIRECT("B"&MATCH(D7,A1:A22)):INDIRECT("B"&MATCH(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"&MATCH(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.

 

[RagDyeR]

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!
=====================================================

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.

 

[Sloth]

=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.