Best excel formula to put daily data into weekly data?

Depends on the version of excel

If you are using the 2007 version, you can simply use the SUMIFS, AVERAGEIFS, COUNTIFS, etc. functions. Link to MS Explanation

If you are using the 2003 version, you will probably want to use the DSUM, DAVERAGE, DCOUNT, etc functions. Link to MS Explanation

If you’re wanting to do pivot tables and/or graphs you might need to make each week’s data fall into an exact date. Ex. Have an equation that makes 10/25/09, 10/24/09, etc into 10/19/09. This could be done by the DATE function and the WEEKDAY function.

@se_ven Gr8!
@davbet27 You may also use subtotals for quick work. That way, it will group your weeks; it will be more easy to view your data. Yeah, use Weekday function in another column. It will ask you serial number (select your date) and return type (enter 2 for starting your week on Monday). So, it will give you weekday as a number from 1 to 7 (Monday=1 through Sunday=7).

If you want to view days instead of the numbers, you may do as following. Put in somewhere, on the same sheet or another, numbers from 1 to 7 and weekdays (Monday, Tuesday,...) in front of the numbers. You may then use VLOOKUP formula to get these days from the numbers.

Welcome to Fluther!

Oh sorry, I forgot about your question.

Easiest way for you could be, load “Analysis Tool Pack” add-in from add-ins. It will add some more functions for you, the one you’d be looking for now is “WEEKNUM” function. This function also takes two arguments/inputs: serial number and return type. Serial number is any valid date (should be in date format). Enter 2 in return type (2 to start week on Monday; 1 or omitted would start the week on Sunday). You’d get the week number. You can insert a column beside your date and put this formula into the new column.

Now, you’ll be ready for using subtotals or pivot tables. For using subtotals, select your data range and choose subtotals.
1. Select the week number from the drop-down list of “At each change in”
2. Use function “Sum”
3. Then “Add subtotal to”, tick/check which you want sum of.
Click OK.
You may collapse by clicking the minus sign of groups and see the summarized weekly data.

The best way is probably pivot tables.
They are a bit tricky to learn, but once you get it, you won’t believe you lived without them.

You will need to add a third column, for week. As Prasad showed, the WEEKNUM formula takes care of it.

Here are a couple of links I Googled up.
How to Create a Pivot Table
Learn to use Pivot Tables in Excel 2007 to Organize Data

Pivot tables are great when you want to play with your data. Well, you can use pivot tables instead of subtotals (there are subtotals in pivot tables too). And, there are some styles and formats that you can use; Excel 2007 has much more styles and formatting options.

I thought you want the summary in your data sheet, and only sum, so I thought of subtotals. If you want your data and summary report in different sheets, pivot table offers more advantages. You can also place pivot table in the same sheet of the data sheet though.

I would like to suggest some things if you’re going to use pivot table. When you’re creating pivot table, you need to enter the input range where your data is. If you enter your data frequently, you’ll have to make changes in your input range from time to time. To avoid this, you may either go for named dynamic range that makes use of OFFSET function or you may use lists/tables. (In Excel 2003, it’s called “List” and it is in Data->List; whereas in Excel 2007, it is called just “Table”, Insert tab->Pivot table/Table. In VBA, it is List object in both Excel 2003 and 2007.)

Dynamic range is useful in creating dynamic charts also, here, I think, List/Table would be more easier for you.

1. To create List/Table, select your data range and create the list from the above menu paths.
2. When creating pivot table, just type the name of your List/Table (you may name it or use the defaults) in where you enter the input range for the pivot table.
(If you’re using dynamic range, paste the name of the dynamic range here)
3. Rest proceed as usual.

So now, when you make changes (add/insert rows, etc.), just go in pivot table and click “Refresh All”, it will take care; you need not worry about the changed data range.