How can data be summed for based on corresponding dates?
 

I want to calculate a rolling annual sum of numerical data based on
corresponding date column.

How can data be summed for based on corresponding dates?
 

Guessing ...

=SUMPRODUCT(--(A1:A100=StartDate),--(A1:A100<=EndDate)*(B1:B100))

A1:A100 contain dates
B1:B100 contain numeric data

StartDate & EndDate are your two date parameters. These could be cells
containing required dates e.g. X1 & X2

=SUMPRODUCT(--(A1:A100=X1),--(A1:A100<=X2)*(B1:B100))

Note SUMPRODUCT does not allow full columns i.e A:A is invalid, and arrays
mut be same size.

HTH

"Hunter" wrote:

I want to calculate a rolling annual sum of numerical data based on
corresponding date column.

How can data be summed for based on corresponding dates?
 

Go for it ;-)

I think you need to provide a little bit more information. Which column has
the data you want to sum ? Which column has the dates ? How do you
calculate the date range ? Is it from today to 365 days ago ? Is a Leap
Year an issue.

I'm guessing you could use SUMIF or maybe SUMPRODUCT

This example sums the values in column B where the date in column A is
greater than today's date minus 365 days.

Regards

Trevor


"Hunter" wrote in message
...
I want to calculate a rolling annual sum of numerical data based on
corresponding date column.

How can data be summed for based on corresponding dates?
 

Supposing you have dates in column A and Amounts in Column B:
=SUMIF(A1:A26,""&DATE(1,1,2006),B1:B26)


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=571930

How can data be summed for based on corresponding dates?
 

Column A is dates corresponding to the numerical value in Column B.

In Column C, I want to calculate the sum of Column B for the previous 365
days.

For now I am ingnoring leap years

"Trevor Shuttleworth" wrote:

Go for it ;-)

I think you need to provide a little bit more information. Which column has
the data you want to sum ? Which column has the dates ? How do you
calculate the date range ? Is it from today to 365 days ago ? Is a Leap
Year an issue.

I'm guessing you could use SUMIF or maybe SUMPRODUCT

This example sums the values in column B where the date in column A is
greater than today's date minus 365 days.

Regards

Trevor


"Hunter" wrote in message
...
I want to calculate a rolling annual sum of numerical data based on
corresponding date column.

How can data be summed for based on corresponding dates?
 

You add column B but you would subtract any data more than 365 days
away.
=SUMIF(A1:A26,"<"&NOW(),B1:B26)-SUMIF(A1:A26,"<"&NOW()-365,B1:B26)


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=571930