CODEWITHSUNDEEP
excel
user3333198
user3333198

Reputation: 105

Sum a daily range into a weekly range

I have a table with daily dates in column A and dividend payments in column B. This looks something like:

6/1/2015     0.05
5/31/2015
5/30/2015
5/29/2015
5/28/2015
5/27/2015
5/26/2015
5/25/2015
5/24/2015
5/23/2015   0.03
5/22/2015
5/21/2015
5/20/2015
5/19/2015
5/18/2015   0.01
5/17/2015
5/16/2015
5/15/2015
5/14/2015
5/13/2015
5/12/2015
5/11/2015
5/10/2015
5/9/2015
5/8/2015
5/7/2015
5/6/2015
5/5/2015
5/4/2015
5/3/2015
5/2/2015
5/1/2015
4/30/2015  0.02
4/29/2015
4/28/2015
4/27/2015
4/26/2015
4/25/2015
4/24/2015
4/23/2015
4/22/2015
4/21/2015

I would like to sum the dividends into a weekly schedule, like in the table below:

6/1/2015     0.05 (= sum of entries in daily table between 6/1 and 5/26)
5/25/2015    0.04 (= sum of entries in daily table between 5/25 and 5/19)
5/18/2015    0.01 (= sum of entries in daily table between 5/18 and 5/12)
5/11/2015         (= sum of entries in daily table between 5/11 and 5/5)
5/4/2015     0.01 (= sum of entries in daily table between 5/4 and 4/28)
4/27/2015         (= sum of entries in daily table between 4/27 and 4/21)
4/20/2015         (= sum of entries in daily table between 4/20 and 4/14)

Basically, if a dividend lies between two dates in the weekly range, then it should be summed into the corresponding cell in column B.

Does anyone have an idea how to tackle this? Many thanks!

Upvotes: 0

Views: 230

Answers (2)

gudal
gudal

Reputation: 337

Use the Weeknum formula to create a third column in your list of dividends per date, so that for each line you have its week number in addition to date. Then on separate tab, you use sumif.

The following named ranges are assumed for convenience. Dividends tab: DividendDate, DividendPaid, DividendWeek Weekly dividends tab WeekNum, TotalDividendWeeløy In DividendWeek you enter the following formula (assume entered in C2, using row 1 for headings) =WEEKNUM(A2) and copy down. If going for over a year, you also need to add a year column.

Then if only doing this for 1 year, you add all week numbers required to Weeknum in weekly dividends tab, and the following formulate to TotalDividendWeekly cells, again assuming you start in row 2 due to headings being in row 1.

=SUMIF(DividendWeek;A2;DividendPaid)

No need for array formula.

NOTE! I use ; as formula separator. Might need to change to , if your language uses that.

EDIT: If you need years, and you probably do now I think of it, you have to add a Year column in the Dividends tab, using the =YEAR(A2) formulate to fill them, and change the SUMIF to a SUMIFS like this:

=SUMIFS(Dvidends;DividendWeek;A2;DividendYear;B2)

This assumes you have created a new column in the Weekly dividends tab in column B, so that the sumif formula is now in column C, and filled it with the correct years, and also created the new named range DividendYear in the Dividends tab.

For fun or if that is nice-to-have for you, you can also create a third tab summing the dividends for the years also, following the same principles as above.

If you want to use a Pivot, you can add the year and date columns as I suggested, create a pivot and then use those new columns to filter on. Good thing about pivot is that it is easy to maniplulate to show what you want. Bad thing is that it is static, so you need to update it to show new numbers. My version you can just scroll to the desired week, and it will always show you the correct values.

Upvotes: 1

TMH8885
TMH8885

Reputation: 888

The first thought I had was to make a pivot table, and then group by Days. From the website http://www.contextures.com/xlPivot07.html

Grouping Dates by Week

To group the items in a Date field by week

-Right-click on one of the dates in the pivot table.
-In the popup menu, click Group
-In the Grouping dialog box, select Days from the 'By' list.
-For 'Number of days', select 7
-The week range is determined by the date in the 'Starting at' box, so adjust this if necessary.

Upvotes: 3

Related Questions