sum of values between two given dates

[saraceno]

good morning
here is my problem
In column A I have some dates, and in column B corresponding
quantities (km ran by a marathon man)
what I need is to compue the sum of km in a given month.
In other words, the SUM(BXX:BYY) function should have, within it a
pointer.
for example, say for september 2008, it should find the first date of
september 2008 (say cell A15), and give to teh sum function the value
15 instead of the XX above. Then look for the latest available date in
september, say A21, and give the sum function the value 21 instead of
YY.
I know/suspect that it has to be done through address, indirect, etc
etc.
but i am lost..

thanx!!

 

[Don Guillett]

How about this idea where 9 is September and your range is dates.

=sumproduct((month(a2:a22)=9)*b2:b22)

 

[Ron Rosenfeld]

good morning
here is my problem
In column A I have some dates, and in column B corresponding
quantities (km ran by a marathon man)
what I need is to compue the sum of km in a given month.
In other words, the SUM(BXX:BYY) function should have, within it a
pointer.
for example, say for september 2008, it should find the first date of
september 2008 (say cell A15), and give to teh sum function the value
15 instead of the XX above. Then look for the latest available date in
september, say A21, and give the sum function the value 21 instead of
YY.
I know/suspect that it has to be done through address, indirect, etc
etc.
but i am lost..

thanx!!

=sumif(B:B,">="&date(2008,9.1)) -
sumif(B:B,">"&date(2008,9,30))

Or the DATE function could be replaced by cell references containing the "from
date" and "to date"
--ron

 

[Thomas Hardy]

good morning
here is my problem
In column A I have some dates, and in column B corresponding
quantities (km ran by a marathon man)
what I need is to compue the sum of km in a given month.
In other words, the SUM(BXX:BYY) function should have, within it a
pointer.
for example, say for september 2008, it should find the first date of
september 2008 (say cell A15), and give to teh sum function the value
15 instead of the XX above. Then look for the latest available date in
september, say A21, and give the sum function the value 21 instead of
YY.
I know/suspect that it has to be done through address, indirect, etc
etc.
but i am lost..

thanx!!

If the first date is in A1 and the first km in B1 then you could have:

C1 = B1

Then use the following formula in C2 and drag it down to the bottom of your
data:

=IF(MONTH(A2)=MONTH(A1);C1+B2;B2)

This will show an increasing total reaching the sum for the month and then
starting again for each new month.
If you use the following conditional formatting in column C turning the font
color to white then only the totals for each month will be shown in black:

Formula is =MONTH(A1)=MONTH(A2)

Thomas

 

[Thomas Hardy]

If the first date is in A1 and the first km in B1 then you could have:

C1 = B1

Then use the following formula in C2 and drag it down to the bottom of
your data:

=IF(MONTH(A2)=MONTH(A1);C1+B2;B2)

This will show an increasing total reaching the sum for the month and then
starting again for each new month.
If you use the following conditional formatting in column C turning the
font color to white then only the totals for each month will be shown in
black:

Formula is =MONTH(A1)=MONTH(A2)

Thomas

Note that this formula:

=IF(MONTH(A2)=MONTH(A1);C1+B2;B2)

was written with the German regional settings so you might need to replace
the semi colons with commas.

 

[Ron Rosenfeld]

=sumif(B:B,">="&date(2008,9.1)) -
sumif(B:B,">"&date(2008,9,30))

There's a typo. Should be:

=sumif(B:B,">="&date(2008,9,1)) - sumif(B:B,">"&date(2008,9,30))
--ron

 

[saraceno]

Guys, thank,
the three methods work!
I had started going through a much moer complicated procedure

thanks a lot

francesco