0

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?

  • 1
    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
2

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.

  • Suggestion: make the sample formula the contents of E2. Change 9 to C2. Then copy the formula down for 52 rows. Stick a formula in D2 to display C2 as a date, and that can also be copied down. Automates the whole table. Use B:B and Z:Z or make make it a table and reference them as Money and Week. – fixer1234 Feb 22 '15 at 18:18
  • Does exactly what I want it to do, and so beautifully. I wasn't aware of the WEEKNUM function, thanks! – Lou Feb 22 '15 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.