sum of values between two given dates

  • Thread starter Thread starter saraceno
  • Start date Start date
S

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!!
 
How about this idea where 9 is September and your range is dates.

=sumproduct((month(a2:a22)=9)*b2:b22)
 
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!!
Click to expand...

=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
 
saraceno said:
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!!
Click to expand...

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 said:
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
Click to expand...
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.
 
=sumif(B:B,">="&date(2008,9.1)) -
sumif(B:B,">"&date(2008,9,30))
Click to expand...

There's a typo. Should be:

=sumif(B:B,">="&date(2008,9,1)) - sumif(B:B,">"&date(2008,9,30))
--ron
 
Guys, thank,
the three methods work!
I had started going through a much moer complicated procedure

thanks a lot

francesco
 
Back
Top