How to create a custom sum series?

Hi,

I have a series of days in column A in sheet 1. Revenue in column C. In sheet 2, I have days grouped up into weeks in column A. A week is every 7 days. How do I make the =Sum function add revenue values in sheet 1 column C for every 7 days so that the dates match?

Here's what the formulas should look like when I pull the right handle down or end result.

=SUM(Day!C3:C9)
=SUM(Day!C10:C16)
=SUM(Day!C17:C23)
=SUM(Day!C24:C30)
=SUM(Day!C31:C37)

So far I've done this:

in a separate scratch sheet

Cell A1 Cell B1
10 16
=A1+7 =B1+7

and so on down to create the correct series in raw numbers. Then I did =CONCATENATE("Day!C",A1,":","C",B1) which gives Day!C10:C16. This is where I'm stuck. How do I go back to sheet 2 and enter =Sum(Day!C10:C16)? =Sum function is looking for a cell not looking at the value parameters I just created.

If it was me, I'd add a WEEKNUMBER helper column

Formula:

Please Login or Register  to view this content.

Then use a pivot table to summarise by week.