# How to summarise costs for one week, given an irregular range of dates and costs?

I have a table of all the money I've spent on given dates, from receipts etc. The dates aren't regular, so the table may look something like:

``````  A            B
╔════════════╦════════╗
1║    Date    ║ Money  ║
╠════════════╬════════╣
2║ 2014-09-01 ║ £4.00  ║
3║ 2014-09-01 ║ £15.00 ║
4║ 2014-09-18 ║ £40.00 ║
5║ 2014-09-27 ║ £3.50  ║
6║ 2014-09-27 ║ £5.00  ║
7║ 2014-10-01 ║ £4.50  ║
8║ 2014-10-04 ║ £3.00  ║
╚════════════╩════════╝
``````

What I want to do is, to create a formula to add up the costs within a 7 day period. So, for example:

``````  C      D            E
╔══════╦════════════╦══════════════╗
1║ Week ║    Date    ║ Amount spent ║
╠══════╬════════════╬══════════════╣
2║    1 ║ 2014-09-01 ║       £19.00 ║
3║    2 ║ 2014-09-08 ║            0 ║
4║    3 ║ 2014-09-15 ║       £40.00 ║
5║    4 ║ 2014-09-22 ║        £8.50 ║
6║    5 ║ 2014-09-29 ║        £7.50 ║
╚══════╩════════════╩══════════════╝
``````

What I want to do in the `Amount Spent` column is to read the `Date` column for the first table, and find all rows which match the date plus 6 (so, in the first row, I want the formula to look for 2014-09-01, 02... up to 07, but when it finds 08, stop counting.)

Then, for each date in the first table which falls within that 7 day period, to look to the `Money` column and add up all the values. So, the `Amount Spent` column should be the sum of all `Money` values in the first table, which fall within the range specified by the `Date` column. I hope this makes sense.

I've tried using MATCH, but I don't think I can get it to work the way I want. This would appear in cell E2:

``````=MATCH(D2, \$A\$2:\$A\$8, 1)
``````

So this would find the row number in the first table that is less than or equal to the current date. But this wouldn't work: say I took the date 2014-09-15. It would point me to A3: 2014-09-01. But this isn't the week of the 15th; nothing was spent on the 15th.

Even if I got that figured out, I don't know how I would then use an array of row numbers to sum the money values. Can you help me to work this one out?

• Have you tried WEEKNUM function? It converts the given date to a week numer and you can choose on which day the week begins. After adding a column with WEEKDAY function you can just use Pivot table to calculate the results you need. – MBu Feb 22 '15 at 13:59

If you add a helper column (let's call it `Z`) containing `WEEKNUM(A2)` (as suggested by MBu), then
``````=SUMIFS(B2:B8, Z2:Z8, 9)
will add the values in column `B` for the rows where column `Z` contains a `9`; i.e., where the date in column `A` is in week 9.