Jul 10 2019 05:40 AM
I am trying to sum a series of data based on all totals from each week. In column G I have a list of quote totals, dollar amounts, In column I there is the "week of" date when the quote was given. Column I will have several with the same date and will only have date increments of 7 days. This report is updated weekly, the dates will always be 7 days apart. I have a formula that will sum on the first date and then the next to add 7 days then add 14 days then 21 days..... Can I tell Excel to automatically keep adding 7 days to the new formula?
Jul 10 2019 06:44 AM
Hi,
I have slightly modified the file, hope it will serve the purpose.
Please see the attached file.
Thanks, Tauqeer
Jul 10 2019 09:16 AM
Great idea, but I think it would be better to use =WEEKNUM(I5) rather than DAY(I5)
The DAY will get repeated in other months e.g 01 Jan 2019 and 01 Oct 2019, whereas Weeknum will not
Jul 10 2019 02:14 PM
And week number is repeating every year. Why don't SUMIF based on date?
=SUMIF($I$5:$I$14,I5,$K$5:$K$14)
Jul 11 2019 04:13 AM
You are quite right Sergei.
In reality, I would never choose to see my weekly totals within the same table as the source data, and have it repeated over several lines.
I would prefer to create a simple Pivot Table based upon the OP's source data, and just add Date as the Row field and Sum of Total Quotes as the Data field.
Jul 11 2019 04:41 AM
Yes Roger, it's always better not to repeat same information and separate reporting from source data