Forum Discussion

Katelyn555's avatar
Katelyn555
Copper Contributor
Jul 10, 2019

Excel Formula with date

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...
excel
Formulas and Functions
tauqeeracma's avatar
tauqeeracma
Steel Contributor
Jul 10, 2019

Katelyn555 

Hi,

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

Please see the attached file.

Thanks, Tauqeer

Roger Govier's avatar
Roger Govier
Brass Contributor
Jul 10, 2019

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 10, 2019

    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)

     

    • Roger Govier's avatar
      Roger Govier
      Brass Contributor
      Jul 11, 2019

      SergeiBaklan 

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 11, 2019

        Roger Govier 

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