sumifs question

[bjsauer]

right now I am working on writing up a sheet that I am using the sumifs function a lot in because it makes it simple to be able to add up cells on another sheet based on date. what I am looking to see if it is possible so that I don't have to go threw 53 different cells to change that date ranges for each formula so that it will calculate what I am looking for. ill be showing an example as reference

=SUMIFS(Daily!F:F,Daily!A:A,">=1/1/18",Daily!A:A,"<=1/7/18")
=SUMIFS(Daily!F:F,Daily!A:A,">=1/8/18",Daily!A:A,"<=1/14/18")


=SUMIFS(Daily!F:F,Daily!A:A,">=1/15/18",Daily!A:A,"<=1/21/18")

what I would like to have happen is for the dates to auto change when I drop down copy the formula threw the rest of the cells. I color changed what I would like to auto fill and change accordingly the date gap that is in these dates is the same gap that I am looking for basically for a years worth of weeks

[ArviLaanemets]

Use defined Tables (Be sure there is no gaps - empty rows or columns - in your datarange. Select any cell in your datarange, and then from Menu Insert > Table. Check 'My table has headers'. OK. The table is created and Design Menu tab is activated. Change the table name in left top in menu bar to meaningful one.)

P.e.
You create a table tDaily on sheet Daily, with header DaySum in column F and DayDate in column A;
On another sheet, you create another table, p.e.
tRepWeekly, with column headers DateFrom, DateTo, WeekSum.
Into columns DateFrom, DateTo, you enter dates for first and last day of week (you can use formulas there too).
Into any of cells in column WeekSum enter the formula
=SUMIFS(tDaily[DaySum],tDaily[DayDate],">=" & [@DateFrom], tDaily[DayDate],"<=" & [@DateTo])
The column WeekSum will be filled with formula automatically.

Whenever you press Tab when last cell of table is selected, or whenever you enter something into next row below table, the table is expanded automatically. All formulas, formats, conditional formattings, and data validations, which apply for whole column of table, are expanded to new row too.

NB. When using Tables, you can't share your workbook for multi-user regime.

Edit. When you enter dates into tRepWeekly manually, and you have at least 2 rows of data in tRepWeekly, select 2 last rows for columns DateFrom and DateTo and drag down - for every row 7 days will be added to both dates.

[Pecoflyer]

@Arvi
Are these duplicate posts and can one be deleted?

@bjsauer
You might also try a Pivot Table if that is OK for your purpose

[ArviLaanemets]

Originally Posted by Pecoflyer

Are these duplicate posts and can one be deleted?

Yes. The one without paragraph starting with "Edit." (the shorter one) is abundant.

Don't understand how the second one was created - but I think I remember that I had to log on anew when editing the post

[Pecoflyer]

Arvi's second post deleted.
I'll keep an eye on other posts to see if this happens again

[bjsauer]

I don't think a defined tables will work in my situation. ive done a lot of research on how to create this workbook as efficiently as possible. to help with coming up with the solution to my problem I have included a copy of the worksheet that I am writing now.

the first page of this workbook is intentionally left blank so that I can input the data on a daily basis, hence the page name daily. The sheet labeled weekly is the sheet that my sumifs question comes into play. the 2 highlighted rows are the 2 left to do date corrections on. if you look at the column labeled total miles it has the correct date configuration done all the way down. I'm looking to see if there is a faster way to write this up so that I don't have to go threw each cell and do date corrections to get the outcome that I am looking for. I'm sorry if I gave any confusion to this question and hopefully now that I have included and shown what I am looking for and explained it a lot better there is an answer that will work for this scenario.

[Debaser]

You could use helper columns for the start and end dates, then refer to them in the SUMIFS formula, or just use a pivot table and group the dates in blocks of 7 days.

[Roger Govier]

@bjsauer
With your dates in column A, just enter

=SUMIFS(Daily!F:F,Daily!A:A,">="&A2,Daily!A:A,"<"& A3)
and copy down.
The dates will adjust in line with the entries you have made for the date in each row.

Note:
One of your formulae must be just "<" and not "<=" otherwise you are duplicting the value being summed