Pulling weekly data out of transaction list?

[GeorgeM]

I have a sheet "Data" that has individual sales transactions, one per
row, including a date/time stamp, and the amount of the transaction,
usually several transactions per day. I want to build another sheet,
"Weekly", that summarizes each week's revenue, adding up the revenue
from all transactions during the week.

My first thought was to set up the Weekly sheet where each row starts
with the first and last date of a week, then do a SUMIF on the
appropriate columns in Data to sum the revenue for all transactions
within that week, but am stuck on what the Criteria field should look
like.

Any suggestions? The Data sheet is refreshed as needed via a database
query, and I don't have much control over its format, but I can put
anything I need in Weekly.

Any help/hints would be appreciated!

Thanks,

George

 

[Guest]

To do that you really want to use a Sumproduct formula... Here is a link to
an invaluable web site (book mark this one)...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

it will look something like...
=sumproduct(--(A1:A100>=Jan1), (--(A1:A100<=Jan7), (B1:B10))

Change jan 1 and jan 7 to the cell references you want...

 

[Debra Dalgleish]

You could create a pivot table to summarize the data, and group the
dates by 7 day intervals. There are instructions and links here:

http://www.contextures.com/xlPivot01.html