Auto adjust a formula in excel

I have an excel file with 3 columns. They are date, units sold and the sum of units sold.
Column E has Sale method.  Cell "E2" would specify the sale method.
There are 2 kinds of Sale methods. One is "Regular" and the other is "Deferred".

Regular Sale method is as follows.
The units are always sold in either June or December. All the months from Jan to Jun are sold in June and from July to December are sold in December. This keeps repeating for every year.

I was able to create a sheet for the Regular sale in the "Regular" worksheet.

Deferred sale is as follows.
There is a 6 months lag in the sale period. The units from Jan to June are sold in December and from July to December are sold in Jan. My required output is shown in "Deferred" Sheet.

I would like to create a formula in a single sheet and a single column ( Column C), which can handle both Deferred or Regular and can give me the output.  The user has to refer to cell "E2" in the formula in column C.
How can I do this?   Please suggest. The file is attached.
Book1.xlsx
vsuripeddiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have come up with this monster formula, see if you get the desired output.

In C2
=IF($E$2="Deferred",IF(MONTH(A2)=6,SUMPRODUCT((MONTH($A$2:$A$25)>MONTH(A2))*(YEAR($A$2:$A$25)=YEAR(A2)-1)*($B$2:$B$25)),IF(MONTH(A2)=12,SUMPRODUCT((MONTH($A$2:$A$25)<=MONTH(A2)-6)*(YEAR($A$2:$A$25)=YEAR(A2))*($B$2:$B$25)),"")),IF($E$2="Regular",IF(MONTH(A2)=6,SUMPRODUCT((MONTH($A$2:$A$25)<=6)*(YEAR($A$2:$A$25)=YEAR(A2))*($B$2:$B$25)),IF(MONTH(A2)=12,SUMPRODUCT((MONTH($A$2:$A$25)>6)*(YEAR($A$2:$A$25)=YEAR(A2))*($B$2:$B$25)),"")),""))

Open in new window

And copy it down.
I have used a conditional formatting to hide the zeros from the formula cells if any.

For detail refer to the attached workbook.
Copy-of-Book1.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vsuripeddiAuthor Commented:
In the Row 31 of C , i.e.  C31 should have a value of 1290.
I do not see 1290 with the formula that is suggested. I could however, se all the otehr values. How can I see 1290 , even though there is no month in the Column A,
See "Differed" tab.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I don't see any sense in expecting the value in col. C if the date column is blank. The formula looks at the date column and then add the col. B accordingly based on your condition set in the cell E2.
If you want to see that value in col. C, you will need to extend the dates in col. A down the rows.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The proposed formula should also work in case the data for some months is not entered.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.