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
Iron 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.

Resources