Excel Formula with date

Question

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.&nbsp; 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?&nbsp;

tauqeeracma · Answer

Katelyn555&nbsp;Hi,I have slightly modified the file, hope it will serve the purpose.Please see the attached file.Thanks, Tauqeer

roger govier · Answer

tauqeeracma&nbsp;&nbsp;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&nbsp; e.g 01 Jan 2019 and 01 Oct 2019, whereas Weeknum will not

sergeibaklan · Answer

Roger Govier&nbsp;
And week number is repeating every year. Why don't SUMIF based on date?
=SUMIF($I$5:$I$14,I5,$K$5:$K$14)
&nbsp;

roger govier · Answer

SergeiBaklan&nbsp;You are quite right Sergei.&nbsp;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.&nbsp;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.

sergeibaklan · Answer

Roger Govier&nbsp;
Yes Roger, it's always better not to repeat same information and separate reporting from source data

Forum Discussion

Excel Formula with date

5 Replies

Resources