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

Question

Hi all,I'm trying to get the summed&nbsp;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 today until 10th September but in 2023. I don't know if my thinking is right where I should 1) Look up all occurrences of a sale for a certain product and then 2) maybe using a sum if to sum it all up when the date is in the next 12 weeks. Any suggestions? I have included some sample data below. Its about 110,000 rows of invoiced orders. Thanks for your help in advance.&nbsp;&nbsp;

martin_angosto · Answer

Excelenthusiast223&nbsp;&nbsp;Hi, please see attached workbook with proposed solution:&nbsp;=SUMIF($A$1:$A$48,"&lt;"&amp;EDATE($E$1,3),$B$1:$B$48)

excelenthusiast223 · Answer

Hi&nbsp;Martin_Angosto&nbsp;&nbsp;How would this work for individual products e.g. just for apples or oranges in my example

martin_angosto · Answer

Excelenthusiast223&nbsp;&nbsp;You would then use SUMIFS instead of SUMIF. See attached.

excelenthusiast223 · Answer

Hi&nbsp;Martin_Angosto&nbsp;&nbsp;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.&nbsp;https://1drv.ms/x/s!AuBDc9CKOEvdgmjv4ygQf_al4NTe&nbsp;

martin_angosto · Answer

Hi&nbsp;Excelenthusiast223 ,&nbsp;Edit: You are right. I completely misread your initial description of the problem. Have corrected the formula.&nbsp;=SUMIFS($G$1:$G$119239,$A$1:$A$119239,"&gt;"&amp;EDATE($N$1,-9),$F$1:$F$119239,K5)&nbsp;Note that -9 comes from -12 months (one year) + 3 months (14 weeks as desired) = -9.&nbsp;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.

Forum Discussion

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

Share

Resources