Forum Discussion

Excelenthusiast223's avatar
Excelenthusiast223
Copper Contributor
Jun 10, 2024

Get sales data for last year but in the next 12 weeks of todays date

Hi all, I'm trying to get the summed sales data for products that occurred in the last year but for the next 12 weeks of todays date e.g. todays date is 10th June 2024, I would want the sales from t...
BI & Data Analysis
excel
Excel for web
office 365
Excelenthusiast223's avatar
Excelenthusiast223
Copper Contributor
Jun 10, 2024

Hi Martin_Angosto 

 

How would this work for individual products e.g. just for apples or oranges in my example

    • Excelenthusiast223's avatar
      Excelenthusiast223
      Copper Contributor
      Jun 10, 2024

      Hi Martin_Angosto 

       

      Thanks for clarifying that. I still don't think I have it right. Can you look the attached file. It is the full data with 2 sumif attempts. 'Test 2' is your formula. Thanks.

       

      https://1drv.ms/x/s!AuBDc9CKOEvdgmjv4ygQf_al4NTe

       

      • Martin_Angosto's avatar
        Martin_Angosto
        Iron Contributor
        Jun 10, 2024

        Hi Excelenthusiast223 ,

         

        Edit: You are right. I completely misread your initial description of the problem. Have corrected the formula.

         

        =SUMIFS($G$1:$G$119239,$A$1:$A$119239,">"&EDATE($N$1,-9),$F$1:$F$119239,K5)

         

        Note that -9 comes from -12 months (one year) + 3 months (14 weeks as desired) = -9.

         

        By the way: I have made the formula with the reference to a "Today's date" cell because using the function TODAY() would make the formulation change each day. I thought it is more convenient to use a reference to a manual-entry-cell for better or more convenient analysis purposes.

Resources