Want to sum amounts by week?

Jeff,

If date entries are like 10-Mar-01, I'd suuggest creating a column with week numbers.

Assuming that dates are in A from A2 on and amounts in B from B2 on,
fill in week numbers in C from C2 on.

In D2 enter: =SUMPRODUCT((WEEKNUM($A$2:$A$100)=C2)*($B$2:$B$100))

and copy down this till the last week number.

Aladin

You suggest creating a column with week number. Is their a way for excel to identify the date entry and assign a week number?

Aladin,

What if I were to want to total by week in a pivot table?

Yes. You must have the Analysis Tool Pak add-in to do this. To check, go to Tools -> Add-Ins, and make sure the the Analysis Tool Pack is check marked. If not, checkmark it.

Then you can use the Weeknum worksheet formula. In the cell that you want the week number, type in =Weeknum(A1)
Where A1 is the date. This tells you what week in the year that the date falls in. Also note that this will number your weeks from 1-52. So, if you have multiple years in your dates columns, this might not work for you because week numbers will be duplicated.

HTH

Jeff,

I think I know why you pose this question. WEEKNUM (for which you need Analysis Toolpak) cannot be used with the SUMPRODUCT formula that I suggested. Mea Culpa.

I suggest doing the following.

You have dates in A from A2 on and amounts in B from B2 on.

In C2 enter: =WEEKNUM(A2) [ copy down this as far as needed ]

In D from D2 on, list all calender weeks (i.e., 1,2,3,4,....) via AutoFill.

In E2 enter: =SUMIF($C$2:$C$100,D2,$B$2:$B$100) [ copy down as far as needed ]

Aladin

> What if I were to want to total by week in a pivot table?

Jeff -- PivotTables is also a good idea. However, the recent SUMIF formula I suggested will certainly suffice.

Regards,

Aladin

Fantastic!

Thank you Dan, I had read once about the weeknum feature but didn't understand it was an add-in. THANKS!