Excel Formula with date

Copper Contributor

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"Annotation 2019-07-10 083821.png 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? 

5 Replies

@Katelyn555 

Hi,

I have slightly modified the file, hope it will serve the purpose.

Please see the attached file.

Thanks, Tauqeer

@tauqeeracma 

 

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

@Roger Govier 

And week number is repeating every year. Why don't SUMIF based on date?

=SUMIF($I$5:$I$14,I5,$K$5:$K$14)

 

@Sergei Baklan 

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.

@Roger Govier 

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