# Sumproduct for Rollover period

#### ynice

##### New Member
Hi all,

Help needed please. Wanted a formula to calculate total sales over a period of 30days. I have attached the file with the desired result i wanted to achieve.

I wanted the agents sales over a period say within 30days to roll over. Thanking you in advance

#### Attachments

• 9.7 KB Views: 9

#### GraH - Guido

##### Well-Known Member
Hi, I fail to understand how you calculate 5 sales for agent 1. Can you explain please?
As far as data calculations go, you can do this date - 30. But that does not look as the solution you are after, hence my question.

#### ynice

##### New Member
Hi Grah,

Thank you for your reply. The result i wanted to achieve is such that the units sold is roll back sum for each agent over a period of 30 days. Am sorry if i couldn't explain it better but it's just kinda complicated. The cal in previous sheet sum 30 days starting from the end date in 2019.10.04 upto start date 2019.11.03. Something similar to what i wanted to achieve is the short table below

Maybe i'll put it this way

 Month Goods SN Begin_date End_Date Quty Formula to get results for Sold in 30Days(Rollback) Feb-19​ Shoes Agent 1 2019-02-03​ 2019-02-16​ 1​ 1​ Mar-19​ Tables Agent 1 2019-03-01​ 2019-03-12​ 1​ 2 i.e (1+1) Mar-19​ Mabbles Agent 1 2019-03-15​ 2019-03-19​ 2​ 4 i.e (1+1+2) Apr-19​ Wall paper Agent 1 2019-04-08​ 2019-04-13​ 2​ 4 i.e (2+2) Such that Feb 1​ March 4​ April 4​

Last edited:

Clear as mud.

#### ynice

##### New Member
I simply wanted to get the total units sold per agent in every 30days. The column H and I have the start and end date. Sorry if this is not too clear but i'll greatly appreciate every suggestion i receive

#### p45cal

##### Well-Known Member
Referring to your attachment, I'm with Grah - Guido, I can't understand how you arrive at your table:

I've tried all sorts of combinations of dates and failed miserably.
Could you explain how you arrive at the results, specifically the 5 for Agent 1. Explain as clearly as you can, which columns of your source table you're using, and exactly which cells contribute to the 5 in your results table, and why.
Without this, I'm getting nowhere.

#### ynice

##### New Member
Hi,

Thank you for your response. Appreciate alot. Kindly please see if this is clear to explain the scenario

 30 days Units sold Agent 1 2019-10-04 to 2019-11-03 1+1+2+1 Agent 2 2019-10-05 to 2019-11-04 1+1+1 Agent 3 2019-10-02 to 2019-11-01 No sales between these date hence for November it should return 2 sold on 2019-11-04 Agent 4 2019-10-02 to 2019-11-01 No sales between these date hence for November it should return 1 sold on 2019-11-05

#### p45cal

##### Well-Known Member
Nope.
Again
Could you explain how you arrive at the results, specifically the 5 for Agent 1. Explain as clearly as you can, which columns of your source table you're using, and exactly which cells contribute to the 5 in your results table, and why.
Why are there different 30 day periods for each Agent? How is this decided?

This is beginning to fascinate me - otherwise I'd have been long gone.

#### ynice

##### New Member
Hi,
This is because it the rollback for just November. All dates for each sales within 30days. Such that end date of the 1st sales to the start date on the last sales within 30days. Is there a way to get a formular for this kind of scenario?

#### p45cal

##### Well-Known Member
That helps a tiny bit, though I still can't find consistency in how your dates are decided.
Yet again:
Could you explain how you arrive at the results, specifically the 5 for Agent 1. Explain as clearly as you can, which columns of your source table you're using, and exactly which cells contribute to the 5 in your results table, and why.

#### ynice

##### New Member
Hi,

Thank you for your reply. Appreciate

Wanted to sum units sold (column J) within every 30days backward for each of the agents (column G).Such that the end date (column I) of the first unit sold to the start date (column H) of the next unit sold is not more than 30days.

Then the result will look like the below.

 30 days Units sold Agent 1 2019-10-04 to 2019-11-03 1+1+2+1 Agent 2 2019-10-05 to 2019-11-04 1+1+1 Agent 3 2019-10-02 to 2019-11-01 No sales between these date hence for November it should return 2 sold on 2019-11-04 Agent 4 2019-10-02 to 2019-11-01 No sales between these date hence for November it should return 1 sold on 2019-11-05

#### Attachments

• 15.1 KB Views: 2

#### ynice

##### New Member
Hi , thanks for your response.

Cell J9,J10,J11,J13

#### ynice

##### New Member
Hi , thanks for your response.

Cell J9,J10,J11,J13

#### p45cal

##### Well-Known Member
All well and good for Agents 1 & 2.
For Agent 3, why are the October dates not 1/Oct/2019 - 31/Oct/2019 1 unit (from cell J7)?
likewise:
For Agent 4, why are the October dates not 1/Oct/2019 - 31/Oct/2019 3 units (from cell J8)?

#### ynice

##### New Member
All well and good for Agents 1 & 2.
For Agent 3, why are the October dates not 1/Oct/2019 - 31/Oct/2019 1 unit (from cell J7)?
likewise:
For Agent 4, why are the October dates not 1/Oct/2019 - 31/Oct/2019 3 units (from cell J8)?
Hi, thanks for your response.

This is because those are for Oct sales. My sample is only calc for Nov. If the date rollback to Sep, then the agent 3 will have that cell calculated for Oct. Since that date in Oct to the next date in Nov is more than 30days, hence that will not count. Same thing apply to Agent 4.