• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

MonthGoodsSNBegin_dateEnd_DateQutyFormula to get results for Sold in 30Days(Rollback)
Feb-19​
ShoesAgent 1
2019-02-03​
2019-02-16​
1​
1​
Mar-19​
TablesAgent 1
2019-03-01​
2019-03-12​
1​
2 i.e (1+1)
Mar-19​
MabblesAgent 1
2019-03-15​
2019-03-19​
2​
4 i.e (1+1+2)
Apr-19​
Wall paperAgent 1
2019-04-08​
2019-04-13​
2​
4 i.e (2+2)
Such that
Feb
1​
March
4​
April
4​
 
Last edited:

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:
60609
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 daysUnits sold
Agent 12019-10-04 to 2019-11-031+1+2+1
Agent 22019-10-05 to 2019-11-041+1+1
Agent 32019-10-02 to 2019-11-01No sales between these date hence for November it should return 2 sold on 2019-11-04
Agent 42019-10-02 to 2019-11-01No 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 12019-10-04 to 2019-11-031+1+2+1
Agent 22019-10-05 to 2019-11-041+1+1
Agent 32019-10-02 to 2019-11-01No sales between these date hence for November it should return 2 sold on 2019-11-04
Agent 42019-10-02 to 2019-11-01No sales between these date hence for November it should return 1 sold on 2019-11-05
 

Attachments

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